CODEWITHSUNDEEP
sql-serverjoin
Jurasick
Jurasick

Reputation: 55

Query hangs when doing a self-join

I have a problem in SQL Server related to comparing rows in the same table for existence of a value. This table is part of a larger query and is being used within an inner join to fetch matching records. My issue is that the CASE expression I am using in the inner join, particularly the part where I am using a self-join with a NOT EXISTS clause, is making the whole query hang. Basically, it keeps going with producing a result. I think something is wrong especially with that part (the self join and the use of the NOT EXISTS clause). Could anyone help me figure out what I am doing wrong? My main issue is that if there is a row present in Tab2 where the ServiceType is 'ELECTRIC' and the DepID is 'DISCON DEP', it should always pick 'ELECTRIC'. In most cases, both 'ELECTRIC' and 'WATER' are present. The unique case, as seen in LocID 500735, is where only 'WATER' is present. I want it to apply the 'WATER' ServiceType here but only if no 'ELECTRIC' is present for that LocID. I am sure there is something wrong with my query. Is there a way to fix it, or is there a better way to achieve what I want the query to do?

My primary table Tab1 (T1) has the following columns and sample data:

Sample data and columns - Tab1

My second table Tab2 (T2) has the following columns and sample data:

enter image description here

I am doing an inner join on the LocID column between Tab1 and Tab2 as:

select T1.LocID, T1.DepID, T2.ServiceType from Tab1 T1 
inner join Tab2 T2
ON CASE 
    WHEN T2.ServiceType='ELECTRIC' AND T1.DepID LIKE '%ELEC%' AND T2.LocID = T1.LocID THEN 1
    WHEN T2.ServiceType='ELECTRIC' AND T1.DepID='DISCON DEP' AND T2.LocID = T1.LocID THEN 1
    WHEN T2.ServiceType='WATER' AND T1.DepID = 'DISCON DEP' AND T2.LocID = T1.LocID AND NOT EXISTS 
        (SELECT 1 FROM Tab2 T20 
        WHERE T2.LocID = T20.LocID 
        AND T20.ServiceType='ELECTRIC'
        AND T20.LocID = T2.LocID) THEN 1
    WHEN T2.ServiceType='WATER' AND T1.DepID LIKE '%WATER%' AND T2.LocID = T1.LocID THEN 1
    WHEN T2.ServiceType='WATER' AND T1.DepID = 'DISCON DEP WAT' AND T2.LocID = T1.LocID THEN 1
    ELSE 0
   END = 1

Upvotes: 0

Views: 109

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

You can simplify that to something like:

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND (
                (T2.ServiceType='ELECTRIC' AND T1.DepID LIKE '%ELEC%')
                OR (T2.ServiceType='WATER' AND T1.DepID = 'DISCON DEP' AND NOT EXISTS 
                                                                                    (SELECT 1 FROM Tab2 T20 
                                                                                    WHERE T2.LocID = T20.LocID 
                                                                                    AND T20.ServiceType='ELECTRIC'
                                                                                    AND T20.LocID = T2.LocID))
                OR (T2.ServiceType='ELECTRIC' AND T1.DepID='DISCON DEP')
                OR (T2.ServiceType='WATER' AND T1.DepID LIKE '%WATER%')
                OR (T2.ServiceType='WATER' AND T1.DepID = 'DISCON DEP WAT')
            )

This will probably not solve your performance issue, to solve that you can try to rewrite your query to a UNION:

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND T2.ServiceType='ELECTRIC' 
        AND T1.DepID LIKE '%ELEC%'

UNION ALL

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND T2.ServiceType='WATER' 
        AND T1.DepID = 'DISCON DEP' 
        AND NOT EXISTS 
                        (SELECT 1 FROM Tab2 T20 
                        WHERE T2.LocID = T20.LocID 
                        AND T20.ServiceType='ELECTRIC'
                        AND T20.LocID = T2.LocID))
            )
UNION ALL

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND T2.ServiceType='ELECTRIC' 
        AND T1.DepID='DISCON DEP'

UNION ALL

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND T2.ServiceType='WATER' 
        AND T1.DepID LIKE '%WATER%'
UNION ALL

SELECT      T1.LocID, T1.DepID, T2.ServiceType 
FROM        Tab1 T1 
INNER JOIN  Tab2 T2
        ON  T2.LocID = T1.LocID
        AND T2.ServiceType='WATER' 
        AND T1.DepID = 'DISCON DEP WAT'

This is more text, but a lot clearer as to what the query does. Also, this query can use indexes much better (if there are indexes, if not, create them).

Upvotes: 1

Related Questions