CODEWITHSUNDEEP
sql-serverjoin
DoIt
DoIt

Reputation: 3428

Returning an empty result set when a match is found between two tables

I am working on a SQL join query as follows

SELECT DISTINCT GL2.Number1, GL.DEPARTMENT,GL.CLASS,GL.SUBCLASS,GL.LINE, PR.*
        FROM DETAIL A

        LEFT JOIN CATEGORIES CAT ON   A.WEB_ID=CAT.CATEGORY_ID  
        LEFT JOIN GROUP_LINES GL ON 
                                                A.BRAND=GL.MANUFACTURER 
                                                OR  A.A1=GL.Number1 
                                                OR  CAT.A2 =  GL.DEPARTMENT
                                                OR  CAT.A3=  GL.CLASS
                                                OR  CAT.A4= GL.SUBCLASS
                                                OR  CAT.A5= GL.LINE


        LEFT JOIN  RULE_GROUPS RG ON GL.GROUP_ID=RG.GROUP_ID
        LEFT JOIN  RULE_GROUPS RG2 ON RG.RULE_ID=RG2.RULE_ID                                            
        LEFT JOIN  GROUP_LINES GL2 ON RG2.GROUP_ID=GL2.GROUP_ID
        LEFT JOIN  PRICING_RULES PR ON RG.RULE_ID=PR.RULE_ID
        LEFT JOIN  PRICING_ATTRIBUTES PA ON PR.RULE_ID=PA.RULE_ID

    WHERE A.INVEN_ID='123456'

Now that I have another table named Exclusions (say Ex) with fields BRAND, Number1, DEPARTMENT, CATEGORY_ID which also the columns in the above query but I would like to check this table and a record should be returned only when the following conditions are all true 

Ex.BRAND!=A.BRAND AND
EX.NEMBER1!=A.SEGMENT1 AND
EX.DEPARTMENT!=GL.DEPARTMENT AND
EX.CATEGORY_ID!=A.WEB_ID

may I know a solution to this?

Upvotes: 0

Views: 30

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

If you want to exclude it if ALL of the columns match:

SELECT DISTINCT GL2.Number1, GL.DEPARTMENT,GL.CLASS,GL.SUBCLASS,GL.LINE, PR.*
        FROM DETAIL A

        LEFT JOIN CATEGORIES CAT ON   A.WEB_ID=CAT.CATEGORY_ID  
        LEFT JOIN GROUP_LINES GL ON 
                                                A.BRAND=GL.MANUFACTURER 
                                                OR  A.A1=GL.Number1 
                                                OR  CAT.A2 =  GL.DEPARTMENT
                                                OR  CAT.A3=  GL.CLASS
                                                OR  CAT.A4= GL.SUBCLASS
                                                OR  CAT.A5= GL.LINE


        LEFT JOIN  RULE_GROUPS RG ON GL.GROUP_ID=RG.GROUP_ID
        LEFT JOIN  RULE_GROUPS RG2 ON RG.RULE_ID=RG2.RULE_ID                                            
        LEFT JOIN  GROUP_LINES GL2 ON RG2.GROUP_ID=GL2.GROUP_ID
        LEFT JOIN  PRICING_RULES PR ON RG.RULE_ID=PR.RULE_ID
        LEFT JOIN  PRICING_ATTRIBUTES PA ON PR.RULE_ID=PA.RULE_ID

    WHERE A.INVEN_ID='123456'
AND NOT EXISTS
(
SELECT *
FROM
Exclusions Ex
WHERE
Ex.BRAND = A.BRAND AND
EX.NEMBER1 = A.SEGMENT1 AND
EX.DEPARTMENT = GL.DEPARTMENT AND
EX.CATEGORY_ID = A.WEB_ID 
);

Or if you want to exclude it if ANY of the columns match

SELECT DISTINCT GL2.Number1, GL.DEPARTMENT,GL.CLASS,GL.SUBCLASS,GL.LINE, PR.*
        FROM DETAIL A

        LEFT JOIN CATEGORIES CAT ON   A.WEB_ID=CAT.CATEGORY_ID  
        LEFT JOIN GROUP_LINES GL ON 
                                                A.BRAND=GL.MANUFACTURER 
                                                OR  A.A1=GL.Number1 
                                                OR  CAT.A2 =  GL.DEPARTMENT
                                                OR  CAT.A3=  GL.CLASS
                                                OR  CAT.A4= GL.SUBCLASS
                                                OR  CAT.A5= GL.LINE


        LEFT JOIN  RULE_GROUPS RG ON GL.GROUP_ID=RG.GROUP_ID
        LEFT JOIN  RULE_GROUPS RG2 ON RG.RULE_ID=RG2.RULE_ID                                            
        LEFT JOIN  GROUP_LINES GL2 ON RG2.GROUP_ID=GL2.GROUP_ID
        LEFT JOIN  PRICING_RULES PR ON RG.RULE_ID=PR.RULE_ID
        LEFT JOIN  PRICING_ATTRIBUTES PA ON PR.RULE_ID=PA.RULE_ID

    WHERE A.INVEN_ID='123456'
AND NOT EXISTS
(
SELECT *
FROM
Exclusions Ex
WHERE
Ex.BRAND = A.BRAND OR
EX.NEMBER1 = A.SEGMENT1 OR
EX.DEPARTMENT = GL.DEPARTMENT OR
EX.CATEGORY_ID = A.WEB_ID 
);

Upvotes: 2

Related Questions