CODEWITHSUNDEEP
mysqlsqldatabaseselect
Zoythrus
Zoythrus

Reputation: 165

How to select records not involved in a join

I need to make a query that returns some records not involved in a join, I paste the tables involved and the query that I wrote.

The tables are CAR and SPACE and the relationship is "one to many" (1 CAR in N SPACES ; in 1 SPACE 1 CAR)

RECORDS OF CAR TABLE:

........................................
car_id  | mark    | model   | prize
........................................
    1   | fiat    | 500     | 15000
    2   | fiat    | panda   | 8000
    3   | opel    | astra   | 20000
    4   | renault | clio    | 14000
    5   | opel    | corsa   | 12000
    6   | fiat    | panda   | 8000
    7   | fiat    | 500     | 15000
    8   | renault | capture | 16000
    9   | renault | clio    | 14000
    10  | fiat    | 500     | 15000

RECORDS OF SPACE TABLE (The primary key is identified by row, column and room_id. car_id is the foreign key of the primary key of CAR's table):

..................................................
   row  | column  | room_id | dimension | car_id
..................................................
    1   | 1       | 1       | 100       | 1
    1   | 2       | 1       | 100       | 1
    1   | 3       | 1       | 100       | NULL
    2   | 1       | 1       | 100       | 1
    2   | 2       | 1       | 100       | 1
    2   | 3       | 1       | 100       | NULL
    3   | 1       | 1       | 100       | NULL
    3   | 2       | 1       | 100       | 6
    3   | 3       | 1       | 100       | 6

Now, What I wanna get from the query is to select all the cars that aren't located in any space. I expect that the result is that table:

........................................
car_id  | mark    | model   | prize
........................................
    2   | fiat    | panda   | 8000
    3   | opel    | astra   | 20000
    4   | renault | clio    | 14000
    5   | opel    | corsa   | 12000
    7   | fiat    | 500     | 15000
    8   | renault | capture | 16000
    9   | renault | clio    | 14000
    10  | fiat    | 500     | 15000

This is the query that I wrote, but it didn't return the results that I expect.

SELECT car.* FROM car,space WHERE car.car_id = space.car_id AND space.car_id IS NULL

I'm learning the logic of the database only recently and unfortunately I'm not good, the query is totally wrong but I don't know how to correct it.

Upvotes: 1

Views: 50

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use not in to check the car id's in cars that are not in the space table. Because you have null values in the car_id column in the space table, the where clause filters them out.

SELECT car.* FROM car
where car_id not in (select distinct car_id from space where car_id is not null)

Or better use not exists.

SELECT c.* FROM car c
where not exists (select 1 from space where car_id = c.car_id)

Upvotes: 3

Andomar
Andomar

Reputation: 238078

You're using conflicting terms in the where clause:

WHERE car.car_id = space.car_id AND space.car_id IS NULL

But space.car_id cannot both be null and match car.car_id.

You're looking for a left join:

SELECT  *
FROM    car
LEFT JOIN
        space
ON      car.car_id = space.car_id
WHERE   space.car_id IS NULL

Upvotes: 5

Related Questions