CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Richard
Richard

Reputation: 1707

SQL Server join across multiple tables

Hopefully an easy MS SQL Question! I have four tables:

Portfolio:
int portfolioId, string portfolioName
1,'PortFolio 1'

Trip:
int tripId, int portfolioId, int tripType, int maxPeople, string tripName
1, 1, 1, 20, 'Trip A'
2, 1, 1, 21, ’Trip B'
3, 1, 2, 22, ’Trip C'

Person:
int personId, int personStatus, string personName
1, 14, ‘Person 1'
2, 15, ‘Person 2'
3, 16, ‘Person 3'

TripPerson:
int personId, int tripId
1, 1
1, 3
2, 1
2, 2

For a given portfolioId, I’m trying to write a clean query that will give me the following columns:

tripId, countA, countB, tripType, tripName

Where: CountA: is the total number of Persons on the Trip. CountB: is the total number of people who are on that trip that have also been on at least one other trip with type of ‘2’. The number of rows returned must match the number of trips related to portfolio where portfolioId = 1, ordered by tripName.

Thoughts? I am using MS SQL, have a basic understanding of SQL, and this is driving me bananas.

Upvotes: 0

Views: 70

Answers (3)

Deepshikha
Deepshikha

Reputation: 10284

You can write a query as:

With CTE1 as
(
-- Total number of persons on a trip:
select count(T.personId) as CountA , tripId
from TripPerson T
group by T.tripId
),
CTE2 as 
(
    -- Total number of people who are on a trip that have also been on 
    -- at least one other trip with type of '2'.
    select Count (T2.personId)as CountB , CTE1.tripId ,CTE1.CountA
    from TripPerson T2
    inner join  TripPerson T3 on T2.personId = T3.personId and T3.tripId =2
    right join CTE1 on CTE1.tripId = T2.tripId
    group by CTE1.tripId,CTE1.CountA
) 
select CTE2.tripId, CTE2.CountA, CTE2.CountB, Trip.tripType, Trip.tripName
from CTE2
inner join Trip on Trip.tripId = CTE2.tripId
inner join Portfolio P on P.portfolioId = Trip.portfolioId

DEMO

Upvotes: 1

Chris Barlow
Chris Barlow

Reputation: 466

This will get you result by joining to tripperson to find the count and using a left join to person again to find the same person who has been on multiple trips.

SELECT t.tripId, count(p.personid) countA, count(p2.personid) countB, 
       t.tripType, t.tripName, t.portfolioid
FROM TRIP t
JOIN TripPerson p ON p.tripid = t.tripid
LEFT JOIN (select tp.personid, count(*) cnt FROM TripPerson tp group by tp.personid) p2 
     ON p2.personid = p.personid and p2.cnt > 1
group by t.tripId, t.tripType, t.tripName, t.portfolioid

Upvotes: 0

Prasad Khode
Prasad Khode

Reputation: 6739

one option is you can use sub-queries to get the countA and countB, so the query looks like some thing below:

select trip.tripId, trip.tripType, trip.tripName, 
(select count(personId) from TripPerson where tripId = trip.tripId) as countA, 
(select count(personId) from TripPerson where tripId IN (trip.tripId, 2)) as countB 
from Trip trip 
where portfolioId = 1 order by trip.tripName

Upvotes: 0

Related Questions