I want to generate a list from 2 Tables based on some conditions in each table.
In Table 1 the primary Key is user_id
In Table 2 this user_id is the foreign Key.
So user_is is the field that these 2 tables have in common.
Now I want to generate a list which is list of articles in Table 1 but only if these articles based on user_id have no password in Table 2.
I have tired this but it is not producing correct results:
SELECT article_id, blog_name, title FROM articles, blogs
WHERE articles.user_id = xyz AND posted2blog = 0 AND articles.user_id = blogs.user_id
AND (blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL) ORDER BY article_id DESC LIMIT 10;
The problem is that it is pulling up articles from those Blogs that do have a password which it should not as per: (blogs.blog_passwd = “” OR blogs.blog_passwd IS NULL)
What is even worst, or an indication of the problem is, it is indicating, via “blog_name”, that it has pulled an article which belongs to a Blog that does not have a password but that article belongs to another Blog all together which does have password!
I assume that title is in the articles table, try this:
SELECT
articles.article_id
, articles.title
, blogs.blog_name
FROM
articles
INNER JOIN
blogs
ON
articles.user_id = blogs.user_id
WHERE
articles.user_id = xyz
AND
articles.posted2blog = 0
AND
(blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
ORDER BY
articles.article_id DESC
LIMIT
10;
If your join matches articles and blogs that don’t have anything to do with eachother, it means you need another field (instead of, or in addition to ‘user_id’) to join the two tables (blog_id for example).
By the way, user_id shouldn’t be the primary key of the ‘articles’ table. Or does each user write only one article?
Still the same error is there.
That is the command which you suggested below is still pulling those articles that belong to blogs which have a password, that means this is not true for them:
(blogs.blog_passwd = “” OR blogs.blog_passwd IS NULL)
so no articles from these blogs should be listed but are being listed!
FYI, user_id is not the primary Key in articles table, the primary key for
that table is article_id. FYI, The users Table and articles table are linked together via the user_id, where user_id is the primary key in users table.
conditions on the blogs table belong in the ON clause and not the WHERE clause. Doing the latter effectively changes your join from a LEFT OUTER to an INNER join.
I tried your new code and it is still not working
That is it is still pulling article from those blogs that have password which
it is not supposed to as per
AND
(blogs.blog_passwd = “” OR blogs.blog_passwd IS NULL)
FYI, here is your new code:
SELECT
articles.article_id
, articles.title
, blogs.blog_name
FROM
articles
INNER JOIN
blogs
ON
articles.user_id = blogs.user_id
AND
(blogs.blog_passwd = "" OR blogs.blog_passwd IS NULL)
WHERE
articles.user_id = xyz
AND
articles.posted2blog = 0
ORDER BY
articles.article_id DESC
LIMIT
10;
No the articles are in Table 1 which is the articles table.
The passwords are in Table 2 which is the Blog table that contains
all sort of customization info about an article such as whether the blog
that it belongs to is password protected.
Would this help point you to a solution, where you make an inner join but restrict the resultant dataset based on you other ‘password’ condition?
SELECT article_id
, blog_name
, title
FROM articles AS a
INNER
JOIN blogs AS b
on b.user_id = a.user_id
AND (
( a.password = ''
OR
a.password = NULL
)
)
You may have to change the alias around because I too am confused about what is in each table.
There is no users table in your query.
If you give info about tables you don’t use in your query, and don’t tell anything about those that you do use, then I can’t help you.
I’m interested in how the users table and articles table are linked. I want to know how the articles table and the blogs table are linked.
Bazz, I’m almost certain his problem is due to the fact that he’s joining the two tables on the wrong field. My guess is that the right field is ‘blogid’, not ‘userid’
WorldNews, does the Blog table have an ID field and does the Articles table have both an ID field and also a BlogID field (the ID of the blog that it is a member of). Can an article feature in more than one blog?
