CODEWITHSUNDEEP
mysqlsql
cefada
cefada

Reputation: 23

How to use "where" in join to two tables?

Main table:

(trip)

id | name
1  | USA
2  | Europe
3  | Asia

Childrens:

(plane)
id | trip_id | name | other
1  |    1    | aaa  |  w
2  |    1    | bbb  |  e
3  |    3    | ccc  |  rr

(boat)
id | trip_id | name
1  |    2    | jjj
2  |    2    | kkk
3  |    3    | lll

If I want to get trips with planes, then I can:

SELECT trip.* FROM trip INNER JOIN plane ON plane.trip_id = trip.id

If I want to get trips with boats, then I can:

SELECT trip.* FROM trip INNER JOIN boat ON boat.trip_id = trip.id

But how to get in one query all trips with planes or boats (or both)?

Upvotes: 1

Views: 89

Answers (5)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Try left joining the trips table to the other two tables. The trick here is that we aggregate at the end by trip and detect if, for each trip, there was a match in the plane or boat tables.

SELECT t.id, t.name,
    CASE WHEN COUNT(p.trip_id) > 0 THEN 'yes' ELSE 'no' END AS has_plane,
    CASE WHEN COUNT(b.trip_id) > 0 THEN 'yes' ELSE 'no' END AS has_boat
FROM trip t
LEFT JOIN plane p
    ON t.id = p.trip_id
LEFT JOIN boat b
    ON t.id = b.trip_id
GROUP BY t.id;

    id  name    has_plane   has_boat
1   1   USA     yes         no
2   2   Europe  no          yes
3   3   Asia    yes         yes

Demo

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

If you want all the result on several rows you can use UNION but Your table plane and boat have not the same number of column so you should use union with explict column name eg:

  SELECT trip_id, plane.name, trip.name
  from plane
  inner join trip on trip.id = plane.trip_id
  UNION 
  SELECT trip_id, plane.name, trip.name
  from bout
  inner join trip on trip.id = boat.trip_id

or if you need all values you must use a null column where needed

  SELECT trip_id, plane.name, trip.name, other
  from plane
  inner join trip on trip.id = plane.trip_id
  UNION 
  SELECT trip_id,plane.name, trip.name, null
  from bout
  inner join trip on trip.id = boat.trip_id

Upvotes: 1

Aamir Nakhwa
Aamir Nakhwa

Reputation: 391

You can simply use Left Join like following

select trip.*, isnull(plane.name, '-') as PlaneName, isnull(boat.name, '-') as BoatName from trip left join plane ON plane.trip_id = trip.id left JOIN boat ON boat.trip_id = trip.id

It will return following table, feel free to use any field from any of these 3 tables...

enter image description here

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

You can achieve by LEFT join

SELECT trip.*,ifnull(plane.Name,'NA') as planetrip, ifnull(boat.Name,'NA')  boattrip FROM trip 
LEFT JOIN plane ON plane.trip_id = trip.id
LEFT JOIN boat ON boat.trip_id = trip.id
WHERE (plane.Name IS NOT NULL OR boat.Name IS NOT NULL)

SQL DEMO: http://sqlfiddle.com/#!9/2ae5c/10

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

Simply use UNION ALL:

SELECT trip.* FROM trip INNER JOIN plane ON plane.trip_id = trip.id
UNION ALL
SELECT trip.* FROM trip INNER JOIN boat ON boat.trip_id = trip.id

Upvotes: 3

Related Questions