[RESOLVED] SQL : Howto get values from the second table related with the first table

Terminator

Maybe a strange topic title,

But I have 3 tables (ads, images, users).
Now I have querie which gives me all ads that are visible (=1) and from the choosen category.

"SELECT a.id, a.userid, a.title, a.views, a.price, a.isvisible, a.date, b.id, b.username  

				 FROM adverts a, users b
				 WHERE a.userid=b.id AND categoryid='" . $_GET['id'] . "' AND isvisible='1'";

This works, however now I want also to get the images which belongs to the ads.
I thought to do this with an Inner Join, however then I get only the ads which have images (so ads with no images are not shown).

"SELECT adverts.id, adverts.title, adverts.views, adverts.isvisible, adverts.date, uploadimages.id, uploadimages.advertid, uploadimages.image 
FROM adverts
LEFT JOIN uploadimages
ON adverts.id=uploadimages.advertid WHERE adverts.categoryid='" . $_GET['id'] . "' AND adverts.isvisible='1' GROUP BY adverts.id ORDER BY date DESC"

How can I get the ads to show with or without images

leatherback

hm.. afaik it should work. You mention an inner join, but in the code you posted a left join. Which did you use? I usually use left joins, and I think they work fine in showing both...

Terminator

Sorry I ment a Left join and indeed it's working fine for adverts which do have images attached, however adverts with NO images attached are not shown 🙁

leatherback

That is odd.

Did you try running the query in mysql directly, e.g., through phpmyadmin? A left join finds all records in the first (left) table, and finds in the other table the records that match the join. If no records are found, it should return NULL for the right table colums.

How do you proceed with processing the data? If you cannot easily run things in mysql, you could simpiy do:

echo "<pre>";
while($row=mysql_fetch_array($result))
  {
  print_r($row);
  }
echo "</pre>";

which would show you exactly what is returned.