I have 4 tables:

//car id_car | name | id_wheel | id_motor | id_transmission --------+-------+----------+----------+----------------- 1 | car_1 | 1 | 1 | 1 2 | car_2 | 2 | 2 | 2 //motor id_motor | name ----------+---------- 1 | motor_01 2 | motor_02 3 | motor_03 //wheel id_wheel | name ----------+---------- 1 | wheel_01 2 | wheel_02 3 | wheel_03 //transmission id_transmission | name -----------------+---------- 1 | trans_01 2 | trans_02 3 | trans_03

In the car table, the fields id_wheel , id_moto , id_transmission refer to corresponding fields in other tables. In addition to these three, there are no links anywhere else.

My task is to write a query script that returns all names of unused parts.

From the beginning, I wrote this:

 select m.name from motor as m left outer join car as c on m.id_motor = c.id_motor where c.name is null;

But when I wanted to add unused parts from the rest of the tables to the same query, I realized that at an impasse since the main table I can have only one ...

Help please write such a request.

    1 answer 1

      SELECT 'motor' part_type, m.name part_name FROM motor m LEFT JOIN car c ON m.id_motor = c.id_motor WHERE c.id_motor IS NULL UNION ALL SELECT 'wheel', w.name FROM wheel w LEFT JOIN car c ON w.id_wheel = c.id_wheel WHERE c.id_wheel IS NULL UNION ALL SELECT 'transmission', t.name FROM transmission t LEFT JOIN car c ON t.id_transmission = c.id_transmission WHERE c.id_transmission IS NULL ORDER BY 1, 2

    It’s somewhat strange to want to get a list in 3 fields - it should turn out that spare parts will be assembled in one record, in fact, they are not related to each other, which is not true ideologically. However, if you really want to - 3 separate subqueries, at the same time number the selected records in them, and connect them by these numbers.

    • and SELECT 'motor' type, m.name name is how? Do I need to substitute something here? - Pavel
    • one
      O_O Nothing should be substituted here ... 2 fields will be placed in the output set, in the first string literal 'motor' with assignment to it the name type, in the second - the value from the field m.name with assignment to it name name. - Akina
    • that's how it is: `LINE 1: SELECT 'motor' type, m.name name` ^ this is copy pastes arrow type indicates - Pavel
    • one
      So this is a reserved word. Replace, for example, with part_type. And just in case, replace the name to part_name ... corrected the request text. - Akina