JOIN issues

[lurtnowski]

I have 6 tables

CREATE TABLE countries (
   country_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   PRIMARY KEY ( country_id )
);
CREATE TABLE states (
   state_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   country_id TINYINT UNSIGNED,
   name VARCHAR(50) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'test@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( country_id ) REFERENCES countries( country_id ),
   PRIMARY KEY ( state_id )
);
CREATE TABLE cities (
   city_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   state_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( state_id ) REFERENCES states( state_id ),
   PRIMARY KEY ( city_id )
);
CREATE TABLE buildings (
   building_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   city_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( city_id ) REFERENCES cities( city_id ),
   PRIMARY KEY ( building_id )
);
CREATE TABLE rooms (
   room_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   building_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( building_id ) REFERENCES buildings( building_id ),
   PRIMARY KEY ( room_id )
);
CREATE TABLE locations (
   location_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   room_id TINYINT UNSIGNED,
   name VARCHAR(25) NOT NULL,
   created_by VARCHAR(50) DEFAULT 'lurtnowski@industechnology.com',
   created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by VARCHAR(50) NULL,
   updated_date TIMESTAMP NULL,
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( room_id ) REFERENCES rooms( room_id ),
   PRIMARY KEY ( location_id )
);

I think all the relationships are right.
I’m trying to get all the lo cations of a country,
my query is

SELECT countries.name AS country_name,
             states.name AS state_name,
             cities.name AS cities_name,
             buildings.name AS building_name,
            rooms.name AS room_name,
            locations.name AS location_name 
FROM locations 
INNER JOIN states ON cities.state_id = states.state_id
INNER JOIN cities ON states.city_id = cities.city_id
INNER JOIN buildings ON cities.building_id= buildings.building_id
INNER JOIN rooms ON buildings .room_id = rooms.room_id
WHERE country_id = 1;  

This seems like it should work, but how would I retrieve the country_name?

[lurtnowski]

SELECT countries.name AS country_name,
             states.name AS state_name,
             cities.name AS cities_name,
             buildings.name AS building_name,
            rooms.name AS room_name,
            locations.name AS location_name 
FROM locations 
INNER JOIN rooms ON locations.room_id = rooms.room_id 
INNER JOIN buildings ON rooms.building_id= buildings.building_id
INNER JOIN cities ON buildings.city_id = cities.city_id
INNER JOIN states ON cities.state_id = states.state_id
INNER JOIN countries ON states.country_id = countries.country_id
WHERE countries.country_id = 1;

I thought that was it, but why does it return all 30 locations when only a few belong to that country?

[Thallius]

So why don’t you debug step by step?

What happens if you only select

`SELECT * FROM countries WHERE countries.country_id = 1;`

If the result is correct then try

SELECT * FROM countries 
INNER JOIN states ON states.country_id = countries.country_id
WHERE countries.country_id = 1;

and so on…

[DaveMaxwell]

No, your relationships aren’t defined enough.

What you’re seeing is you’ve got one of the following

1. The same room in multiple buildings
2. The same building in multiple cities
3. The same city in multiple states
4. The same state in multiple countries

So say you’ve got a building in Portland. Is that Portland, Maine or Portland, Oregon. Is it Kansas City, Kansas or Kansas City, Missouri?

At the very least, buildings needs more granular FKs. state_id if not state_id AND country_id

[r937]

@lurtnowski

FROM locations
INNER JOIN states ON cities.state_id = states.state_id

this drives me bananas

please read just those first two lines of the FROM clause, and tell me what you were thinking when you wrote them

you need rigour for SQL

here’s how to think of the FROM clause –

start with the most restricted table, the one that you want the fewest rows from, and join from there

in your case it’s country, because of the WHERE condition

so i would write your FROM clause as follows –

  FROM countries
INNER
  JOIN states
    ON states.country_id = countries.country_id
INNER
  JOIN cities
    ON cities.state_id = states.state_id
INNER
  JOIN buildings   
    ON buildings.city_id = cities.city_id
INNER
  JOIN rooms   
    ON rooms.building_id = buildings.building_id   
INNER
  JOIN locations
    ON locations.room_id = rooms.room_id 
 WHERE countries.country_id = 1;

notice how each table joined has an ON clause that relates the newly-joined table to one that was previously mentioned

this rigorous approach is not only easier when writing a query, but ~way~ easier for someone to understand what the query is doing

[lurtnowski]

If I write the query like that, its not going to affect performance?

[r937]

database optimizers are smart enough to choose the best performing execution plan no matter how you write the query

i just prefer writing it in the least confusing way

clarity of purpose, eh