[Zwidar]

I am pretty new to SQL. I can do most of the basic stuff or figure it out from the internet. I've come across something I haven't had much luck finding online. I'm trying to do a segment where the results only meets specific criteria. I did come up with a solution at the end, but have some concerns.

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.

[andrewsw]

I assume it could be like this rough draft:

select distinct *
from persontable
join gifttable
on persontable.personid = gifttable.personid
where (gifttable.giftcode = 9004 or gifttable.giftcode = 9015) and 
persontable.personid not in 
(select gifttable.personid 
from persontable 
join gifttable
on persontable.personid = gifttable.personid
where gifttable.giftcode <> 9004 and gifttable.giftcode = <> 9015)

but I'm tired and am not able to explore further, soz

[Zwidar]

andrewsw, on 10 July 2013 - 05:04 PM, said:

select distinct *
from persontable
---join gifttable
---on persontable.personid = gifttable.personid
---where (gifttable.giftcode = 9004 or gifttable.giftcode = 9015) and 
persontable.personid not in 
(select gifttable.personid 
from persontable 
join gifttable
on persontable.personid = gifttable.personid
where gifttable.giftcode <> 9004 and gifttable.giftcode = <> 9015)

I do like the <> in the subquery, will save me much typing! Thanks for that, I didn't think about that at all.

The lines I marked with --- seem unnecessary to me. Why would I need a where clause for those 2 gift codes, if I'm already removing everything except those 2 gift codes in the subquery?
Other than that it seems similar to my final statement, guess I have to use a subquery then?

[andrewsw]

Did you try it without the commented lines? You might be right

You need a subquery because you effectively need to pass through the dataset twice (for each current row).