How to fix this simple SQL query?

I have a database with three tables:

• user_table
• country_table
• city_table

I want to write ANSI SQ开发者_如何学PythonL which will allow me to fetch all the user data (i.e. user details including the name of the country of the last school and the name of the city they live in now).

The problem I am having is that I have to use a self join, and I am getting slightly confused.

The schema is shown below:

CREATE TABLE user_table (id int, first_name varchar(16), last_school_country_id int, city_id int);

CREATE TABLE country_table (id int, name varchar(32));

CREATE TABLE city_table (id int, country_id int, name varchar(32));

This is the query I have come up with so far, but the results are wrong, and sometimes, the db engine (mySQL), asks me if I want to show all [HUGE NUMBER HERE] results - which makes me suspect that I am unintentionally creating a cartesian product somewhere.

Can someone explain what is wrong with this SQL statement, and what I need to do to fix it?

SELECT usr.id AS id, usr.first_name, ctry1.name as loc_country_name, ctry2.name as school_country_name, city.name as loc_city_name
                FROM user_table usr, country_table ctry1, country_table ctry2, city_table city
                WHERE usr.last_school_country_id=ctry2.id
                      AND usr.city_id=city.id
                      AND city.country_id=ctry1.id
                      AND ctry1.id=ctry2.id;

Try this. I wrote it using ANSI syntax for clarity. I assume that you may not always have usr.city_id or usr.last_school_country_id, so I used a left outer join meaning you will always get usr records back regardless.

I also removed and ctry1.id=ctry2.id, because that would require the user's current city to be in the same country as their last_school_country_id, which I don't think is always the case.

SELECT usr.id AS id, usr.first_name, ctry1.name as loc_country_name, ctry2.name as school_country_name, city.name as loc_city_name 
FROM user_table usr
left outer join city_table city on usr.city_id=city.id 
left outer join country_table ctry1 on city.country_id=ctry1.id 
left outer join country_table ctry2 on usr.last_school_country_id=ctry2.id 

This query selects all users whose city is in the same country as their last school:

SELECT  usr.id AS id, usr.first_name, ctry1.name as loc_country_name, ctry2.name as school_country_name, city.name as loc_city_name
FROM    user_table usr
JOIN    city
ON      city.id = usr.city_id
JOIN    country_table ctry1
ON      ctry1.id = city.country_id
JOIN    country_table ctry2
ON      ctry2.id = usr.last_school_country_id
WHERE   ctry1.id = ctry2.id

It is synonymous to your original query.

As long as all fields named id are primary keys, this query cannot return more records than there are in user_table.

Make sure that all id are PRIMARY KEYs and you don't have duplicates.

Could you please run these queries:

SELECT  COUNT(*)
FROM    user_table

SELECT  COUNT(*), COUNT(DISTINCT id)
FROM    city

SELECT  COUNT(*), COUNT(DISTINCT id)
FROM    country_table

and post the output here?

normally if you're joining 3 tables there will be two joining statements. always 1 less then the number of items being joined.

Never mind, I understand why you might need the join, going to keep working at the code.

SELECT user_table.*, contry_table.*, city_table.* FROM user_table, country_table, city_table WHERE country_table.id = last_school_country_id AND city_id = city_table.id