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.

 

[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