CODEWITHSUNDEEP
sqloracle-database
74H1R
74H1R

Reputation: 3514

How to skip matched records in outer join?

I have a scenario where I want to skip matched records against a particular product in outer joined table. What thing I am missing in my query? What else I have to mention in my where clause to achieve desired query.

Customer_Profile table.

 USER_ID        
 110            
 111    
 112
 113
 114

Services table.

 USER_ID            PRODUCT_NAME
 110                'Daily Offer'
 119                'Daily Offer'
 120                'Daily Offer'
 110                'Another Offer'

Required result set. If 110 opted for 'Daily Offer', I want to skip it only. If 110 opted for another offer, it will be the part of required result set. 

 111    
 112
 113
 114

Query I tried: 

SELECT C.USER_ID FROM CUSTOMER_PROFILE C 
LEFT OUTER JOIN SERVICES S ON C.USER_ID = S.USER_ID
WHERE (S.PRODUCT_NAME <> 'Daily Offer' or S.PRODUCT_NAME is NULL)

Which is giving following output. How can I skip 110 USER ID?

 110
 111    
 112
 113
 114

Upvotes: 0

Views: 297

Answers (4)

Ishamael
Ishamael

Reputation: 12795

SELECT C.USER_ID
FROM CUSTOMER_PROFILE C
LEFT OUTER JOIN (
    SELECT MAX(PRODUCT_NAME = 'Daily Offer') has_offer, USER_ID
    FROM SERVICES GROUP BY USER_ID
) S ON C.USER_ID = S.USER_ID WHERE has_offer = 0 OR has_offer IS NULL;

MAX(PRODUCT_NAME = 'Daily Offer') per user will be True if and only if the user has at least one daily offer, which I believe is exactly what you want.

EDIT: This, however, will print the user only once if he has more than one offer. You can print him multiple times by adjusting the query slightly:

SELECT C.USER_ID
FROM CUSTOMER_PROFILE C
LEFT OUTER JOIN (
    (
        SELECT MAX(PRODUCT_NAME = 'Daily Offer') has_offer, USER_ID
        FROM SERVICES GROUP BY USER_ID
    ) F
    INNER JOIN SERVICES S
    ON F.USER_ID = S.USER_ID
) ON C.USER_ID = S.USER_ID
WHERE F.has_offer = 0 OR F.has_offer IS NULL;

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Why Not this

SELECT C.USER_ID 
FROM CUSTOMER_PROFILE C 
WHERE C.USER_ID NOT IN( SELECT S.USER_ID
                        FROM SERVICES S 
                        WHERE S.PRODUCT_NAME = 'Daily Offer' OR S.PRODUCT_NAME is NULL
                        GROUP BY S.USER_ID   
                        HAVING COUNT(S.USER_ID) = 1
                      )

FIDDLE DEMO

Upvotes: 1

Tristan
Tristan

Reputation: 1024

Just change the join to not equal to like this:

SELECT C.USER_ID 
FROM CUSTOMER_PROFILE C 
LEFT OUTER JOIN SERVICES S ON C.USER_ID <> S.USER_ID
OR (C.USER_ID = S.USER_ID AND S.PRODUCT_NAME <> 'Daily Offer')

Upvotes: 0

Jaugar Chang
Jaugar Chang

Reputation: 3196

I think this is what you really want:

SELECT C.USER_ID,s.* 
FROM CUSTOMER_PROFILE C 
LEFT OUTER JOIN SERVICES S 
ON C.USER_ID = S.USER_ID and S.PRODUCT_NAME <> 'Daily Offer'
WHERE  S.PRODUCT_NAME is NULL

With s.* to see what's the difference between your code and mine.

Sql Fiddle here.

Upvotes: 1

Related Questions