I have a database with three tables:
user_table
country_table
city_table
I want to write ANSI SQL 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;
