CODEWITHSUNDEEP
sqloracle-databasejoinselect
Tuonelan Joutsen
Tuonelan Joutsen

Reputation: 23

Joining two indirectly related tables without including columns from tables in between

Based on the following ERD, I'm trying to write a query that displays reservations made directly by customers and include reservation id, reservation date, customer id, customer first name, tour trip id, tour category name, tour trip date

enter image description here

I've been able to include everything in my query except for the tour category name because the TOUR_SITES table is in the way. Is there a way I can join the category name from the TOUR_PACKAGE table without adding any extra columns?

  SELECT  r.RESERVATION_ID, r.RESERVATION_DATE, 
c.CUSTOMER_ID, c.FIRST_NAME,t.TRIP_ID, o.TRIP_DATE/*, P.CATEGORY_NAME*/

FROM CUSTOMER c 
INNER JOIN RESERVATION r
ON
r.CUSTOMER_ID=c.CUSTOMER_ID

INNER JOIN TOURTRIP_RESERVATION t
ON
r.RESERVATION_ID=t.RESERVATION_ID

INNER JOIN TOUR_TRIP O
ON
t.TRIP_ID=o.TRIP_ID


WHERE AGENT_ID IS NULL;

Upvotes: 0

Views: 96

Answers (2)

VTi
VTi

Reputation: 1319

You need to first join tour_sites with tour_trip on TOUR_SITEID and then join tour_package with tour_sites on category_id to get the tour category_name. You can join left join on tour_sites in case there are no tour sites assigned for a tour trip like a newly added tour_trip.

SELECT  r.RESERVATION_ID, r.RESERVATION_DATE, c.CUSTOMER_ID, c.FIRST_NAME,t.TRIP_ID, o.TRIP_DATE,TP.CATEGORY_NAME
FROM CUSTOMER c 
INNER JOIN RESERVATION r ON r.CUSTOMER_ID=c.CUSTOMER_ID
INNER JOIN TOURTRIP_RESERVATION t ON r.RESERVATION_ID=t.RESERVATION_ID
INNER JOIN TOUR_TRIP O ON t.TRIP_ID=o.TRIP_ID
LEFT JOIN TOUR_SITES TS ON TS.TOUR_SITEID =  O.TOUR_SITEID
INNER JOIN TOUR_PACKAGE TP ON TP.CATEGORY_ID = TS.CATEGORY_ID 
WHERE AGENT_ID IS NULL;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

Is there a way I can join the category name from the TOUR_PACKAGE table without adding any extra columns?

Sure; just join all tables you need. You don't have to add additional columns (the ones you don't need) into the select column list, but you do have to use the tour_sites table anyway.

Something like this:

select  r.reservation_id, r.reservation_date, 
  c.customer_id, c.first_name,t.trip_id, o.trip_date,
  p.category_name
from customer c inner join reservation r on r.customer_id=c.customer_id
inner join tourtrip_reservation t on r.reservation_id=t.reservation_id
inner join tour_trip o on t.trip_id=o.trip_id
--
join tour_sites s on s.tour_siteid = o.tour_siteid       --> add 
join tour_package p on p.category_id = s.category_id     --> this
where agent_id is null;

Upvotes: 1

Related Questions