How to write a sql to get the record in this dynamic table?

Question

I have a table to store the dynamic attribute

The structure is like this:

id    itemid  value
----  ------  ------
1     1       A
2     2       B
3     3       C
4     1       B
5     2       C

So when I pass the multi-valued parameter

Pass value A B result itemid 1
Pass value B result itemid 1 2
Pass value B C result 2

How can I write this sql? By the way i use mysql

Can you make the question clearer please? Also include what you have tried so far. — Mike Baxter, Mar 20 '13 at 14:07
@Teifi sorry i use ipad to post this topic，its hard to edit， i just want to how to write the sql ， i really dont know how to select in this table — chanjianyi, Mar 20 '13 at 14:12
@AarolamaBluenk that's not really fair; I answer on my iPad and it is extremely frustrating. Even comments are a pain in the ass. — Aaron Bertrand, Mar 20 '13 at 14:25
@AaronBertrand I would still try to type it out even if it was on my 4 inch Android screen. — Kermit, Mar 20 '13 at 14:26
Regardless of the method used to post the question, it should still be clear enough to read. I still don't understand the question. — Mike Baxter, Mar 20 '13 at 14:27
@chanjianyi What do you mean 'pass'? I've never heard that in the context of SQL before so I'm not sure what you mean. Please edit your question to make it clearer! — Mike Baxter, Mar 20 '13 at 14:29
Removed the gratuitous and large image (remember that imgur is blocked for some users) and replaced it with equivalent text. Sorry @ChrisLively. — Aaron Bertrand, Mar 20 '13 at 14:30
@AarolamaBluenk well I guess you're better than most of us. :-) — Aaron Bertrand, Mar 20 '13 at 14:30
@AaronBertrand I'm in the lowest cast system there could be. — Kermit, Mar 20 '13 at 14:31
@Teifi pass means the condition... Like the answer 1 , plz see — chanjianyi, Mar 20 '13 at 14:31
More to the point, if you "pass" in `B C`, shouldn't the result be 2? Or am I missing something? — Gordon Linoff, Mar 20 '13 at 14:48

score 2 · Accepted Answer · answered Mar 20 '13 at 14:58

2

Assuming that (itemid, value) has a UNIQUE constraint:

SELECT itemid
FROM tableX
WHERE value IN ('A', 'B')       -- the list
GROUP BY itemid
HAVING COUNT(*) = 2 ;           -- the size of the list

answered Mar 20 '13 at 14:58

ypercubeᵀᴹ

113,259
19
174
235

score 1 · Answer 2 · answered Mar 20 '13 at 14:15

1

You should be able to get the behaviour you are looking for using a SQL INTERSECT query. Here is a brief example.

SELECT itemid FROM mytable WHERE value = 'A' INTERSECT SELECT itemid FROM mytable WHERE value = 'B'

answered Mar 20 '13 at 14:15

Christian Dahlqvist

1,665
12
9

Thx but I search this function in google ，it seems only for oracle? I use mysql – chanjianyi Mar 20 '13 at 14:29
This may help: http://stackoverflow.com/questions/2621382/intersect-in-mysql – Christian Dahlqvist Mar 20 '13 at 14:31

How to write a sql to get the record in this dynamic table?

2 Answers2