Can this be done, query three tables

I have three tables

create table utilities (
   utility_id SMALLINT NOT NULL AUTO_INCREMENT,
   ...
   PRIMARY KEY ( utility_id )
);

create table mdps (
   mdp_id SMALLINT NOT NULL AUTO_INCREMENT,
   utility_id SMALLINT,
   ...
   FOREIGN KEY ( utility_id ) REFERENCES utilities( utility_id ),
   PRIMARY KEY ( mdp_id )
);

create table generators (
   generator_id SMALLINT NOT NULL AUTO_INCREMENT,
   mdp_id SMALLINT,
   ...
   FOREIGN KEY ( mdp_id ) REFERENCES utilities( mdp_id ),
   PRIMARY KEY ( generator_id )
);

and this join works

SELECT generators.name AS generator_name,
generators.type AS generator_type,generators.manufacturer,
generators.model,generators.capacity,
generators.trip_rating,generators.frequency,
generators.output_voltage, 
generators.fuel_type,
generators.max_fuel_usage_rate,
generators.quantity_of_fuel_storage,
generators.notes,
generators.created_by,
generators.created_date,
generators.updated_by,
generators.updated_date,
generators.enabled, mdps.name AS mdp_name, 
mdps.mdp_id 
FROM generators 
INNER JOIN mdps ON generators.mdp_id = mdps.mdp_id 
WHERE generator_id = 1

How can i get 2 records from the utility table (name, utility_id)
I think I can cause the three tables are connected (right?)

yes, mdps has a many-to-one relationship with utility, so just add another join

2 Likes