Join problem with a where statement

[michelleqw]

Dear sql users,

We do have two tables with a field year and we want to make a join with a where statement year.

Our tables:
zcities:
id_city city_name year
1 NY 2003
2 LA 2004
3 CAL 2004

znames
name id_city year
john 1 2003
mary 2 2003
milly 4 2004

If we make a join without a where statement like:

SELECT znames.name, zcities.city_name
FROM znames LEFT JOIN zcities ON znames.id_city = zcities.id_city;

we will see a correct view and milly doesn´t have a city (correct).

The problem will come if we are selecting year=2004

SELECT znames.name, zcities.city_name
FROM znames LEFT JOIN zcities ON znames.id_city = zcities.id_city
WHERE (((znames.year)=2004) AND ((zcities.year)=2004));

Nothing will be shown and we hoped to see milly without city as above!

What do we do wrong?
Are we making a mistake?
Can someone show us the right sql source?

Nice regards,

Michelle.

 

[PHV]

You may try this:
SELECT znames.name, zcities.city_name
FROM znames LEFT JOIN zcities
ON znames.id_city = zcities.id_city AND znames.year = zcities.year
WHERE znames.year=2004

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swampBoogie]

SELECT znames.name, zcities.city_name
FROM znames LEFT JOIN zcities 
ON (znames.id_city = zcities.id_city
AND zcities.year=2004)
WHERE znames.year=2004

 

[michelleqw]

Thanks PHV for the good information.

Michelle.