SQL Help Needed 1

[Thadeus]

Hello all,
Here's the background:
I have a db with serial numbers and color codes.
Any serial that has the following codes may have a duplicate serial with a different code in the db.

codes: XAB, XAC, XAD

I need the serials that have one of these three codes, but do NOT have a duplicate serial...

Table: tblPRODUCT
Serial Code
1234 XAB
1234 ZAZ
2234 XAB
3234 XAB
3234 XCZ
4234 ZSZ

The serial I need to see in my output is 2234 because it does have code XAB, but it does NOT have a duplicate serial with some other code.

I'm looking for a single SQL query to complete this (nesting/subqueries are fine).

Not sure why my head won't go around this this morning, but it won't and I'm getting myself frustrated...

~Thadeus

 

[cfw2]

This query should work.

select
serial
,code
from tblproduct
where code in ('XAB','XAC','XAD')
and serial in ( select
serial
from tblproduct
group by serial
having count(*) = 1 )

 

[Thadeus]

Thanks cfw2. I will try this on Monday... I did come up with something different, but it was still running when I left today. If it works I'll post it Monday. I will give yours a try as well and update this thread.

Thanks again.
~Thadeus

 

[Thadeus]

cfw2,
Your solution worked and was more efficient than the other solution (so there is no point in publishing the less efficient query).

I thank you for saving me time and grant you a star for your efforts.

Thank you,
~Thadeus