another query

[poe]

databes tables:

 

#1 = user

id | name

1 | chris

2 | mike

3 | jim

 

 

#2 = banners

id | url | filename | user_id | zone_id

1 | sony.com | style.jpg | 1 | 1

2 | nike.com | swoosh.jpg | 1 | 2

3 | mazda.com | zoomzoom.jpg | 2 | 1

4 | microsoft.com | windows.jpg | 3 | 4

 

 

#3 = zones

id | description

1 | mainpage:top

2 | feature:side bar

3 | blog:bottom

4 | sponsored:listing

 

 

i want to list all zones, and get the data for the user.

ie. my query for user 1 is:

$qx = " SELECT z.id, z.description, b.id, b.url, b.filename, b.user_id, b.zone_id ";
$qx .= " FROM zones AS z ";

$qx .= " LEFT JOIN banners=b ON b.zone_id = z.id "; 

$qx .= " WHERE b.user_id = 1 ";

 

 

however this only lists the 2 zones that user 1 has a banner, i want it to list all for zones and where user 1 does not have a banner, just leave blank

[artacus]

You can't have the b.user_id in the where clause and expect it to behave like a left join

$qx = "SELECT z.id, z.description, b.id, b.url, b.filename, b.user_id, b.zone_id 
FROM zones AS z
LEFT JOIN banners AS b ON z.id = b.zone_id 
    AND b.user_id = 1
WHERE 1 = 1 --just here so you see where";

[fenway]

You can't have the b.user_id in the where clause and expect it to behave like a left join

Because the LEFT JOIN has the effect of "nulling out" the joined table -- in this case, banners -- so you won't be able to examine the column's value in the WHERE clause.