SQL select problem

March 20, 2006 3:30 AM

help me with this SQL select

Below is a small sample from a large table. From it I need to select the Product ID's that are only in Category A and B.

prodId catId

101 A
101 B
101 C
101 D
102 A
102 B
103 A
103 B
103 C
104 A
104 B
105 C
106 A
106 B
107 B
107 C
108 C
108 D

So from the sample only product's 102, 104 106 would qualify. I thought this would be simple but it's wrecking my head.

posted by oh pollo! to computers & internet (24 comments total)

SELECT productid,MIN(catid) AS catmin,MAX(catid) AS catmax FROM table GROUP BY productid HAVING catmin='A' AND catmax='B'

Or something like that.
posted by cillit bang at 3:48 AM on March 20, 2006

Select Distinct (ProdID) from table where CatID in ('A', 'B')?
posted by kenaman at 3:51 AM on March 20, 2006

ummm... Am I missing something here? Or can you not just do:

select (*) from table where catid='A' or catid='B'

??
posted by antifuse at 4:04 AM on March 20, 2006

select (*) from table where catid='A' or catid='B'

Antifuse - that will return duplicate prodIds as both will return 102, 103, 104 etc.
posted by kenaman at 4:07 AM on March 20, 2006

It's ugly, but this would work:

select a.prodid from table
where catid='A'
and prodid in (select prodid from table where catid='B')
and prodid not in (select prodid from table where catid not in ('A','B'))
posted by blue mustard at 4:09 AM on March 20, 2006

oops, the first line shoule read:
select prodid from table

(not "a.prodid"... )
posted by blue mustard at 4:10 AM on March 20, 2006

Kenaman and Antifuse: that approach will return productID's which belong to Categories A and B but will *also* return productID's which belong to other Categories e.g, From the example table productID 101 would be returned - but this would be wrong as it is also a member of categories C and D. I want products that are ONLY members of Category A and B.

Cillit bang: That is close but no cigar as it is also returning Categories between A and B. (I simplified the categories for the question - sorry, shouldn't have done this. The categories are also Integers)
posted by oh pollo! at 4:11 AM on March 20, 2006

That is close but no cigar as it is also returning Categories between A and B

This is getting very hacky, but it'll work:

SELECT productid,MIN(catid) AS catmin,MAX(catid) AS catmax, COUNT(DISTINCT catid) AS count FROM table GROUP BY productid HAVING catmin='A' AND catmax='B' AND count=2
posted by cillit bang at 4:18 AM on March 20, 2006

One more possibility:

select prodid
from table
group by prodid
having
max(case catid when 'A' then 1 else 0 end)*
max(case catid when 'B' then 1 else 0 end)*
min(case when catid in ('A','B') then 1 else 0 end)>0
posted by blue mustard at 4:20 AM on March 20, 2006

Oh, I missed the "A and B" together part. I am smrt.

this

select (prodid) from (select prodid from table where catid='a') as a inner join (select prodid from table where catid='b') as b on a.prodid=b.prodid

should do it.
posted by antifuse at 4:21 AM on March 20, 2006

(If there are no duplicates you can drop the DISTINCT, which should be faster)
posted by cillit bang at 4:21 AM on March 20, 2006

I was wrong.....I think Blue Mustard had it the first time...
posted by kenaman at 4:21 AM on March 20, 2006

Oh crap, I screwed the pooch again... hehehe... I missed the "only" in your second answer. Jebus. This is hard!
posted by antifuse at 4:24 AM on March 20, 2006

This approach is cleaner and can be easily extended to 3 or more categories:

SELECT SUM(catid!='A' and catid!='B') AS otherspresent, SUM(catid='A') AS apresent, SUM(catid='B') AS bpresent FROM table GROUP BY productid HAVING otherspresent=0 AND apresent!=0 AND bpresent!=0
posted by cillit bang at 4:29 AM on March 20, 2006

presuming MySQL here since it wasn't specified

As a refinement of cillit bang's first query, you can do this without having to do that nasty max/min stuff. This also extends to any number of categories:

SELECT prodId FROM table GROUP BY prodId HAVING GROUP_CONCAT(catId ORDER BY catId ASC) = 'A,B'
posted by Rhomboid at 4:31 AM on March 20, 2006

Oh, the catId's aren't letters? Well that changes things. I wouldn't use the GROUP_CONCAT method with integers. Why did you post that way if it's not how the data is?
posted by Rhomboid at 4:33 AM on March 20, 2006

Sorry for trying to simplify it - I don't know what I was thinking. Cillit Bang gets best answer - I'm not saying later suggestions didn't work just that Cillit's was the easiest to change for my exact requirements. Thanks to everyone.
posted by oh pollo! at 4:45 AM on March 20, 2006

Well, when simplifying it has the effect of making some solutions not work, it will cause issues. At the very least, when simplifying, you should keep the same data type.
posted by antifuse at 5:51 AM on March 20, 2006

select prodId from table where catId = 'B' and prodId in (select prodId from table where catId = 'A')
posted by aneel at 9:12 AM on March 20, 2006

select prodId from test
group by prodId
having sum(case catId when 'A' then 1 when 'B' then 2 when 'C' then 4 when 'D' then 8 end) = 3
posted by jockc at 11:57 AM on March 20, 2006

aneel, that won't work. The requirement was that the product be in categories A and B only -- yours will return products that are in both A and B but could also be in C or D, such as 101.
posted by Rhomboid at 2:34 PM on March 20, 2006

You could also tackle this problem with the minus command.

select distinct prodID from table_name
where catId = A and catId = B
minus
select distinct prodID from table_name
where catId = C or catId = D

The obvious flaw with this query is that it will break if you add more categories to the table, but it should provide the result you're looking for.
posted by sockpup at 4:10 PM on March 20, 2006

Ah. You're right. I misread the question. Sorry.
posted by aneel at 12:14 AM on March 21, 2006

This is would work, but is probably not terribly efficient:

select prodId from table
where catId = 'B'
and prodId in (select prodId from table where catId = 'A')
and prodId not in (select prodId from table where catId != 'A' and catId != 'B')
posted by aneel at 12:23 AM on March 21, 2006