LEFT JOIN (MySQL)

Need help!

[tata668]

I thought I knew how to use LEFT JOIN but I'm not so sure anymore!

=============
The problem:
=============
I want to list the users with their country and region (If the user's country has no regions, NULL would be returned).

ex:

userId = 123
country = USA
region = New York

userid = 456
country = Japan
region = NULL

Japon has no regions (in my application) so "NULL" is returned.

=============
The Tables:
=============

:::user::: (the user's informations)
user_ID
user_Country
user_Region ("0" if the user's country has no regions)

:::country:: (the countries)
country_ID

:::countrytext::: (the name of the country depending on the current selected language):
countrytext_CountryID
countrytext_Lang
countrytext_Text

:::countryregion:: (a region associated to a country)
countryregion_ID
countryregion_CountryID (not used in this example)

:::countryregiontext::: (the name of the region depending on the current selected language)
countryregiontext_RegionID
countryregiontext_Lang
countryregiontext_Text

=========
The query
=========
(Let's say the current language is "2")

SELECT user_ID, countrytext_Text, countryregiontext_Text
FROM user, country, countrytext

LEFT JOIN countryregion, countryregiontext
ON user_Region = countryregion_ID
AND countryregiontext_RegionID = countryregion_ID
AND countryregiontext_Lang = 2

WHERE user_Country = country_ID
AND countrytext_CountryID = country_ID
AND countrytext_Lang = 2

=========
The result
=========

Instead of listing each user one time only, with his country and region (or NULL if there is no region in the user's information), I receive more than one listing by user!

Can someone help me? Why is my query wrong?

[phoenix_fly]

Hey buddy,

Sure, I´m no expert too, but it seems to me your join syntax is wrong. When you say:

...
FROM user, country, countrytext
LEFT JOIN countryregion, countryregiontext
ON user_Region = countryregion_ID
...

You should perform incremental joins, but one at a time, you see:

FROM ( tableA LEFT JOIN tableB on fieldX=fieldY )
LEFT JOIN tableC on fieldZ=fieldX

...and on (add parenthesis on the prior ones for each incremental join)

[tata668]

Hey phoenix_fly,
you were right about the wrong syntax. I changed my query and now it works!

Thanks a bunch!

-----

SELECT user_ID, countrytext_Text, countryregiontext_Text
FROM user, country, countrytext

LEFT JOIN countryregion
ON user_Region = countryregion_ID
LEFT JOIN countryregiontext
ON countryregiontext_RegionID = countryregion_ID
AND countryregiontext_Lang = 2

WHERE user_Country = country_ID
AND countrytext_CountryID = country_ID
AND countrytext_Lang = 2

[phoenix_fly]

Sure, pal. It´s been a pleasure to help.

By the way, put the conditions in the WHERE, and not in the join statement:

SELECT user_ID, countrytext_Text, countryregiontext_Text
FROM user, country, countrytext

LEFT JOIN countryregion
ON user_Region = countryregion_ID
LEFT JOIN countryregiontext
ON countryregiontext_RegionID = countryregion_ID
AND countryregiontext_Lang = 2 /* NOT HERE */

WHERE user_Country = country_ID
AND countrytext_CountryID = country_ID
AND countrytext_Lang = 2
AND countryregiontext_Lang = 2 /* BUT HERE */

Also, a good practice is to make reference to the tables that host the fields, like:

tableA.field1

This way, if you have fields with the same name in different tables, mysql won´t be confused (he´ll complain if there are). And, also, for me it feels more clear to understand the structure, when you look at the code in the future.

[tata668]

It doesn't work if I put "AND countryregiontext_Lang = 2" in the WHERE clause... It only returns users that have both a country AND a region.

I don't use the "tableA.field1" notation because I start each of my columns' name by the table name, so I can't have a conflict. I know some people say it's not a good practice, that it's better to use short names for the column, but it works pretty well for me!

Thanks again for the help...