Help with Table Join

[mds1256]

$nid = 65        ------- an example

 

 

Select title, body, picdescription, piclink, date_format(date, '%D %M %Y' ) as convDate from news, newspic where news.id = $nid and newspic.newsid = $nid

 

This query works perfect when the linked record is in both tables, but if the record does not exist in the 'newspic' table it doesnt even return the result from the 'news' table.

 

Please bear in mind the way this works is to return all the results from 'newspic' table (which could be multiple results) where the news.id and newspic.newsid matches.

 

Any idea how i can return the result even if the newspic table contains no matching records

 

I have tried an LEFT JOIN but cannot work out the exact way to do it?

 

Thanks

 

 

[mikosiko]

try:

SELECT title, 
       body, 
       picdescription, 
       piclink, 
       date_format(date, '%D %M %Y' ) as convDate 
FROM news
    LEFT JOIN newspic ON newspic.newsid = news.id
WHERE news.id = $nid

 

assuming that newspic.newsid is the foreign key to news (newspic.newsid = news.id).... otherwise replace with the right field holding the relationship.

[mds1256]

  Quote

 

assuming that newspic.newsid is the foreign key to news (newspic.newsid = news.id).... otherwise replace with the right field holding the relationship.

 

Order a pint and im paying lol

 

Perfect

 

Thanks again!!!!