how to select records that are a group?

[amota]

Hi,

I have a table like this:

ITEMID CODE
1 1
1 2
1 3
2 1
2 3
2 4
3 1
3 2
4 3

where every item is a set of codes. For example,
ItemID 1 = (1,2,3)
ItemID 2 = (1,3,4)
ItemID 3 = (1,2)
ItemID 4 = (3)

How can I know if an item exists? For example, if (1,2,3) exists or if (1,2) exists?

I'm working with a MySQL Database, and VB.Net application.

Thanks.

 

[KarveR]

select code from table where id='1'
your result will be 3 items, 1,2 and 3.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

KarveR, i think amota is asking for a query to identify which ItemIDs have all three codes 1,2,3

select ItemID
  from daTable
 where code in (1,2,3)
group
    by ItemID
having count(*) = 3

r937.com | rudy.ca

 

[KarveR]

that did cross my mind after I posted actually ....

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

they won't necessarily have a count of 3 from the example tho, which is where my brain overloaded.

If for example you had to search for a set of codes (1,3), 2 results would be returned unless you correctly specify the count.

hope amota is good with VB

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[steven290]

try this

select group_concat(code) from table group by itemid

 

[amota]

Thanks to all for your responses.

What I need to know is the exact group.
For example, If I look for (1,2,3) it should return ItemID=1
If I search for (1), it should return none.
If I look for (1,2), it should return itemID=3

 

[r937]

my query gives you the exact group

r937.com | rudy.ca

 

[KarveR]

r937 your query will if the having count = criteria is set correctly.

I don't know if VB has any array counting functions which could be used to asses the value for that part of the query dynamically, otherwise it means manually adding that value every time.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

yes, and you're right about the dynamic aspect



r937.com | rudy.ca