CODEWITHSUNDEEP
sql-serverjoin
Jurasick
Jurasick

Reputation: 55

Joins on separate columns

I am writing a query that is part of a larger data extraction query. The part I am stuck with is best explained as below:

Table1 (T1) columns

Table3 (T3) columns

Table6 (T6) columns

T1 is the primary table. T3 and T1 are joined using LocationID and ServiceCategory. However, there are possible duplicates for this combination in T3. I am therefore using:

LEFT JOIN(select distinct T3.LocationID,T3.ServiceCategory from Table3 where ServiceStatus in (2,3,4)) T3

ON T1.LocationID=T3.LocationID and T1.ServiceCategory=T3.ServiceCategory

Of course now I don't have T3.ServiceType to join with Table6. I want to keep the record count from Table1 intact when I eventually get down to a join between T3 and T6. I am using T6.ServiceRate in a further join so it is not particularly important for my question. I am basically getting stuck with the way the tables are designed and how I need to create the joins.

I have a secondary problem where the ServiceCategory in Table3 is shared by two types of ServiceType records. Within a LocationID, I only want one of those ServiceCategory records to be retrieved in my SELECT statement. The differentiator is the ServiceType which is either W or I. The reason I am using a LEFT JOIN is because not all T1.LocationID / T1.ServiceCategory records will have a match, so no INNER JOIN is possible.

I am looking for some help with these problems. Happy to share some sample data if needed.

Sample Data:

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 52

Answers (1)

aboSH
aboSH

Reputation: 26

Not sure, if I understood right. You mean that T3.ServiceTypecould be W or I?

Maybe, you could add a construct with not EXISTS:

SELECT
*
FROM
T1
LEFT JOIN(select distinct T3.LocationID,T3.ServiceCategory,T3 from Table3 where ServiceStatus in (2,3,4)) T3 
ON T1.LocationID=T3.LocationID and T1.ServiceCategory=T3.ServiceCategory

WHERE
(
T3.ServiceType = 'W' 
OR NOT EXISTS (SELECT TOP 1 1 from Table3 T3n WHERE T3n.ServiceStatus in (2,3,4) and T1.LocationID=T3n.LocationID and T1.ServiceCategory=T3n.ServiceCategory and T3n.ServiceType <> T3.ServiceType)
)

Upvotes: 1

Related Questions