Another SQL Query thread

[TwoSaints]

Really struggling with getting the result i need from an SQL Query -

I am using the following query to retrieve all the news in a database and eventually display it into a html table:

SELECT news.id, news.status, news.title, news.date, news.userid, users.realname
FROM news, users 
WHERE news.userid = users.id
ORDER BY date DESC

Now this works fine, that is unless the user that posted the news has been deleted, in which case that news item is just not shown. So I tried the following

SELECT news.id, news.status, news.title, news.date, news.userid, users.realname
FROM news, users 
WHERE news.userid = users.id 
OR news.userid NOT IN (SELECT id FROM users)
ORDER BY date DESC

Which does display the news posted by a deleted user, however it displays it once for every existing user.

How can I get this fixed so I get every news item just once and the realname just left blank if not found.

 

[Izi]

use the LEFT JOIN statement

SELECT news.id, news.status, news.title, news.date, news.userid, users.realname
FROM news
LEFT JOIN users ON users.userid = news.userid
ORDER BY date DESC

unless i have misunderstood

 

[HungryHippos]

You're over-complicating it, you need to join your NEWS and USERS tables to get back USER information relating to your NEWS data.

Easiest way is to create a new query then right click the query in the window and choose the option to Design Query.

Now in the Query Designer, right click, add tables

Add your NEWS and USERS tables, then ensure they are linked by the relevant key, i.e. news.userid and users.id need to be linked if they are relevant.

Now you can just tick which fields you want from both tables, it will return for example the new items and the user details relevant to that news item.

 

[TwoSaints]

use the LEFT JOIN statement

SELECT news.id, news.status, news.title, news.date, news.userid, users.realname
FROM news
LEFT JOIN users ON users.userid = news.userid
ORDER BY date DESC

unless i have misunderstood

You've understood perfectly and that is the solution I was after! Thanks very much