SQL self join query

advertisements

I have four tables:

Service (serviceId(PK), serviceTypeId, capacity)
Facilitator (facilitatorId(PK), facilitatorName)
ServiceType (serviceTypeId(PK), serviceType)
ServiceFacilitator (serviceId(PK), facilitatorId(PK)) (join entity)

The service types in the 'ServiceType' table at present are Hotel and Conference Centre

The 'capacity' column in the 'Service' table contains numbers whick are only meaningful when related to ServiceType.serviceTypeId, i.e. represent either beds or seats depending on the type of service.

I need to write a query which will return 3 columns, nammely: facilitatorName, Beds, Seat

I'm almost certain that creating and discarding temporary tables in a stored procedure is a poor solution which may have negative ramifications for future scalability etc and that the solution involves a self-join on the Service table.

So far, my query reads as follows but returns no rows; what am I missing?

SELECT f.facilitatorName,(SUM(ts1.capacity)) as Beds,(SUM(ts2.capacity)) as Seats

FROM dbo.Facilitator as f,
        dbo.TestService as ts1,
        dbo.TestService as ts2,
        dbo.ServiceFacilitator as sf

WHERE f.facilitatorId = sf.facilitatorId AND
        (sf.serviceId = ts1.serviceId or
        sf.serviceId = ts2.serviceId) and
        ts1.serviceId = ts2.serviceId and
        ts1.serviceTypeId = '1' and -- type hotel
        ts2.serviceTypeId = '2' -- type conference centre           

GROUP BY f.facilitatorName

Breaking the query into two as such will return accurate results for Beds and Seats:

SELECT f.facilitatorName,(SUM(ts1.capacity)) as Beds

FROM dbo.Facilitator as f,
        dbo.TestService as ts1,
        dbo.ServiceFacilitator as sf

WHERE f.facilitatorId = sf.facilitatorId AND
        sf.serviceId = ts1.serviceId and
        ts1.serviceTypeId = '1'         

GROUP BY f.facilitatorName

SELECT f.facilitatorName,(SUM(ts2.capacity)) as Seats

FROM dbo.Facilitator as f,
        dbo.TestService as ts2,
        dbo.ServiceFacilitator as sf

WHERE f.facilitatorId = sf.facilitatorId AND
        sf.serviceId = ts2.serviceId and
        ts2.serviceTypeId = '2'         

GROUP BY f.facilitatorName

Thank you..


A simple solution to your query should be:

SELECT f.facilitatorName,
       SUM(case when ts.serviceTypeId = '1' then ts.capacity end) as Beds,
       SUM(case when ts.serviceTypeId = '2' then ts.capacity end) as Seats
FROM dbo.Facilitator as f
LEFT JOIN dbo.ServiceFacilitator as sf ON f.facilitatorId = sf.facilitatorId
LEFT JOIN dbo.TestService as ts ON sf.serviceId = ts.serviceId
GROUP BY f.facilitatorName