mysql query on two tables

Question

What SELECT query can i use to only show the cars WHERE bookings.status <> 1

so on the table below, cars.id (1, 3, 4, 6) will only show as the result

i'm stuck with this query or of this is any good:

SELECT * FROM `cars` as `C` INNER JOIN `bookings` AS `B` ON `C`.`id` = `B`.`id` ....?

cars

  id | name
  -- | -------------
  1  | Car 1
  2  | Car 2
  3  | Car 3
  4  | Car 4
  5  | Car 5
  6  | Car 6

bookings

id | car_id | status
-- | ------ | ------
1  | 1      | 0
2  | 2      | 1
3  | 2      | 2
4  | 1      | 0
5  | 5      | 1

EDIT: sorry i wasn't clear here, i also want the others listed as result even though they are not on the bookings table

Answer 1

SELECT A.* , B.* FROM cars A, bookings B WHERE B.car_id=A.id AND B.status <> 1

Answer 2

SOLVED IT with this query

SELECT * FROM `cars` WHERE `id` NOT IN  (SELECT `B`.`car_id` FROM `cars` AS `C` INNER JOIN `bookings` AS `B` ON `C`.`id` = `B`.`car_id` WHERE `B`.`status` = 1)

thanks everyone, it helped alot from your answers

Answer 3

Try this :

SELECT * FROM `cars` as `C` INNER JOIN `bookings` AS
 `B` ON `C`.`id` = `B`.`id` where `B`.`status` <> 1

Try this if you didn't want records from Cars that are not in Bookings :

SELECT * FROM `Cars` as `C` Right JOIN `Booking` AS
 `B` ON `C`.`id` = `B`.`id`

Answer 4

Assuming your car_id from your bookings table is the same column as the id in the cars table...

SELECT A.* from cars A
inner join bookings B on A.id=B.car_id
where b.status<>1;

Answer 5

Try This...

SELECT * FROM cars C LEFT JOIN bookings B ON C.id = B.car_id  WHERE B.status <> 1

Answer 6

try this

SELECT * FROM `cars` C, `bookings` B WHERE C.id=B.car_id AND B.status!=1

Answer 7

This will only show the cars

SELECT c.name FROM `cars` as `C` INNER JOIN `bookings` AS
     `B` ON `C`.`id` = `B`.`id` where `B`.`status` != 1