CODEWITHSUNDEEP
sqlsql-serverjoin
Cody Robinson
Cody Robinson

Reputation: 19

Having trouble joining tables

I'm trying to write a query to display the name of each passenger with their fare. Here's what I have so far -- it gives no errors, but also no results:

-- Write a query to display the Name of each passenger as well as the fare for their trip.
select passenger.name, departure_info.fare * passenger.quantity AS passenger_fare
from passenger, seat_passenger, manages, departure_info, seat_info
where departure_info.Dept_id=manages.Dept_id 
AND manages.Seat_id=seat_info.Seat_id 
AND seat_info.Seat_id=seat_passenger.Seat_id 
AND seat_passenger.Pass_id=passenger.pass_id

Here is the relationship view of the tables https://i.sstatic.net/dmhiJ.png

Upvotes: 0

Views: 56

Answers (1)

roman
roman

Reputation: 117380

using proper joins syntax will make this query from your query

select
    p.name, di.fare * p.quantity as passenger_fare
from passenger as p
   inner join seat_passenger as sp on sp.pass_id = p.pass_id
   inner join seat_info as si on si.seat_id = sp.seat_id
   inner join manages as m on m.seat_id = si.seat_id
   inner join departure_info as di on di.dept_it = m.dept_id

I suggest you to turn inner joins into left outer joins and see what you're missing

select
    p.name, di.fare * p.quantity as passenger_fare,
    sp.pass_id, si.seat_id, m.seat_id, di.dept_id
from passenger as p
   left outer join seat_passenger as sp on sp.pass_id = p.pass_id
   left outer join seat_info as si on si.seat_id = sp.seat_id
   left outer join manages as m on m.seat_id = si.seat_id
   left outer join departure_info as di on di.dept_it = m.dept_id

Actually, you could even remove unused joins (if you don't miss some records from seat_info):

select
    p.name, di.fare * p.quantity as passenger_fare
from passenger as p
   inner join seat_passenger as sp on sp.pass_id = p.pass_id
   inner join manages as m on m.seat_id = sp.seat_id
   inner join departure_info as di on di.dept_it = m.dept_id

Upvotes: 1

Related Questions