CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
user3692735
user3692735

Reputation: 3

SQL Query - excluding rows where one value of a column with multiple values matches

I have a set of tables as follows. (This is somewhat simplified, several of these tables have some other columns, but I've tried to pare it down) 

Website
ID     |
_____________________________________

10001
10002
10003

Domain Names
Name                | Website_ID
_____________________________________
surfinglikewoah.com | 10001
surfinglikewoah.net | 10001
ihatesurfing.com    | 10002
surfinghatesme.net  | 10003 

Promotion Redemptions
Promo ID            | Website_ID 
_____________________________________
0001                | 10001
0002                | 10001
0003                | 10001
0002                | 10002


Promo Codes
Promo ID            | Code 
_____________________________________
0001                | test
0002                | super-discount
0003                | double-discount

And I'm trying to filter OUT any accounts that have the promotion "test" - however, because these websites are associated with multiple promotion redemptions, the wrong rows still appear in my results. NOTE: I'll be filtering based on additional criteria, again with columns that may have multiple values per row, or may even be null.

Here's the query so far: 

select distinct
ws.id, 
ARRAY(select distinct dn.name FROM domainname dn WHERE ws.id = dn.website_id), 
ARRAY(select p.code FROM promotionredemption pr, promotion p WHERE p.id = pr.promotion_id        AND pr.site_id = ws.id) 

from website ws 
left join domainname dn on dn.website_id = ws.id
left join promotionredemption pr on pr.site_id = ws.id
left join promotion p on p.id = pr.promotion_id


where p.code != 'test'

order by 
   ws.id asc

This query should NOT be returning the surfinglikewoah.com result - but it does, because that result ALSO has two other promotion redemptions that do not match 'test'. 

Results: 
10001 | {surfinglikewoah.com, surfinglikewoah.net} | {test, super-discount, double-discount}  
10002 | {ihatesurfing.com} | {super-discount} 
10003 | {surfinghatesme.net} | {}

I feel like I'm missing something obvious!

Help is greatly appreciated.

Upvotes: 0

Views: 1353

Answers (2)

Steve Bryant
Steve Bryant

Reputation: 1046

What you want to find is one record where another record does not exist. The last two words of the last sentence are the key NOT EXISTS. I find a lot of code that uses outer joins that I think would be better served by using an EXISTS clause.

The following query may not be exactly what you need, but hopefully it should get you close enough that you can adjust it to fix your exact needs.

 select ws.id
    ARRAY(select distinct dn.name FROM domainname dn WHERE ws.id = dn.website_id), 
    ARRAY(select p.code FROM promotionredemption pr, promotion p WHERE p.id = pr.promotion_id        AND pr.site_id = ws.id) 
 from   website ws
 where  not exists (
        select      1
        from        promotion p
        left join   promotionredemption pr
            on  p.id = pr.promotion_id      
        where       pr.site_id = ws.id
            and p.code = 'test'
    )
 order by
    ws.id asc

Upvotes: 1

Ali Adravi
Ali Adravi

Reputation: 22803

ARRAY(select p.code FROM promotionredemption pr, promotion p WHERE p.id = pr.promotion_id AND pr.site_id = ws.id)

Change this to:

ARRAY(select p.code FROM promotionredemption pr, promotion pp 
      WHERE pp.id = pr.promotion_id        
       AND pr.site_id = ws.id
       AND PP.Id = P.ID)

Upvotes: 0

Related Questions