SQL query help

[BigP]

Hello!

 

I\'ve been trying to optimize a few of my queries in a program I wrote but I\'m getting a little lost. Here is a half written SQL query:

 

  $sql = \'SELECT e.id, e.posterid, e.titletext, m.username\'

       . \' FROM phpnews_news e, phpnews_posters m\'

       . \' WHERE e.posterid = m.id\'

       . \' ORDER BY e.id DESC\';

       

       $SQL_query = mysql_query($sql);

       

       while ($posts = mysql_fetch_array($SQL_query))

       {

         // Do stuff

       }

My tables are like so:

 

phpnews_news table holds all the news.

phpnews_posters table holds people allowed post news.

 

The name of the person who posts the news is in the phpnews_posters table. A foreign key, \"posterid\" is used to link them to an article. There\'s also a field called \"postername\" that stores the postersname in the phpnews_news table in case that particular news poster is deleted.

 

So, as you can see I am able to get the posters name from the phpnews_posters table, but I don\'t know how to use the \"postername\" field from phpnews_news if there is no matching ID in the posters table.

 

So basically if this isn\'t true:

. \' WHERE e.posterid = m.id\'

 

Any help is greatly appreciated! You\'d be helping me make an Open Sorce project better by helping me here!

 

Thanks!

[sirmanson]

You need to use an outer join : Here is an example

 

SELECT e.id, e.posterid, e.titletext, m.username

FROM phpnews_news e

LEFT JOIN phpnews_posters m ON e.posterid = m.id

ORDER BY e.id DESC

 

This will pull all of the data from phpnews_news and only the records in phpnews_posters that match. If you wanted to reverse this (pull all from posters and only those in news that match) you could change it to a RIGHT JOIN

 

http://www.mysql.com/doc/en/JOIN.html

[Barand]

SELECT e.id, e.posterid, e.titletext, m.username

FROM phpnews_news e

LEFT JOIN phpnews_posters m ON e.posterid = m.id

ORDER BY e.id DESC

 

You need the name from both tables. If there is a null in the m.username (ie no natching record) then use e.username