persontable
personid, name
1, john doe
2, jane doe
3, john smith
4, jane smith
5, bob jones
gifttable
giftid, personid, amount, giftcode
1, 1, 10, 9001
2, 1, 20, 9015
3, 2, 10, 9004
4, 2, 20, 9015
5, 3, 20, 9004
6, 4, 10, 9002
7, 4, 10, 9004
8, 5, 10, 9001
I want a segment for anyone with ONLY giftcode 9004 or 9015. Which should only return personid 2 and 3.
This was my initial segment.
select distinct * from persontable join gifttable on persontable.personid = gifttable.personid where gifttable.giftcode = 9004 or gifttable.giftcode = 9015
The results gave me personid 1, 2, 2, 3, 4.
I changed it to this:
select distinct * from persontable join gifttable on persontable.personid = gifttable.personid where (gifttable.giftcode = 9004 or gifttable.giftcode = 9015) and (gifttable.giftcode <> 9001 or gifttable.giftcode <> 9002)
same results of personid 1, 2, 2, 3, 4.
Reading w3schools and other sites for command options, I came up with this not in subquery idea.
select distinct * from persontable where persontable.personid not in (select gifttable.personid from persontable join gifttable on persontable.personid = gifttable.personid where gifttable.giftcode = 9001 or gifttable.giftcode = 9002)
Now this code works, the results are personid 2 and 3.
There are a few concerns I have with this statement. One I've read that subqueries can be quite slow and should be avoided. The gifttable is a couple million records, so slowness worries me. Second concern is the giftcode, there are about 50 giftcodes I would have to exclude. So while I could write "or gifttable.giftcode = xxx" 50 times, I'd prefer to not do that and I have concerns of that also slowing down the segment as well.
Is there a better and easier way to write the code to do what I'm attempting? Or should I stick with what I have working and make it work for my full segment?
Thanks.