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

Mahmood Rehman · Accepted Answer · 2013-12-04 05:41:13Z

1

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`

edited Dec 4, 2013 at 5:41

answered Dec 4, 2013 at 5:02

Mahmood Rehman

4,3377 gold badges43 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

alisongaleon Over a year ago

this will give me the opposite result that i wanted to have, it will return the cars.id(2,5)

Mahmood Rehman Over a year ago

means you want to get the result where status equal to 1 ?

alisongaleon Over a year ago

sorry i wasn't clear here, i mean it will only show me the list of cars that are status <> 1, in this case only the cars.id (1), i also want the others listed as result even though they are not on the bookings table

Mahmood Rehman Over a year ago

show me in comments what result you expect.did you try left join ?

alisongaleon Over a year ago

yes and same result, basically it will only show the cars that are listed on the bookings table. for example the car.id 6, will not show on the result because it has no bookings.car_id 6.

|

Noor · Accepted Answer · 2013-12-04 05:05:35Z

1

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

answered Dec 4, 2013 at 5:05

Noor

1,4019 silver badges28 bronze badges

Comments

Satish Sharma · Accepted Answer · 2013-12-04 05:06:30Z

0

try this

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

answered Dec 4, 2013 at 5:06

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

Comments

naveen goyal · Accepted Answer · 2013-12-04 05:07:58Z

0

Try This...

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

answered Dec 4, 2013 at 5:07

naveen goyal

4,6272 gold badges18 silver badges26 bronze badges

1 Comment

alisongaleon Over a year ago

it will only show the cars that are listed on the bookings table. for example the car.id 6, will not show on the result because it has no bookings.car_id 6.

Thirumalai murugan · Accepted Answer · 2013-12-04 05:38:49Z

0

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;

edited Dec 4, 2013 at 5:38

Thirumalai murugan

5,9448 gold badges34 silver badges54 bronze badges

answered Dec 4, 2013 at 5:11

user3064395

1

Comments

alisongaleon · Accepted Answer · 2013-12-04 05:48:52Z

0

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

answered Dec 4, 2013 at 5:48

alisongaleon

1532 gold badges4 silver badges13 bronze badges

Comments

wild · Accepted Answer · 2013-12-04 06:02:32Z

0

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

answered Dec 4, 2013 at 6:02

wild

3401 gold badge3 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql query on two tables

7 Answers 7

6 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

6 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related