On Mar 15, 2011, at 6:18 PM, Rhino wrote:


 All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL.

--
Rhino

your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right?
result would be
2607
1520
8934
7295
1649
8871




On 2011-03-15 18:51, LAMP wrote:
Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_id    org_id
34        2607
34        2607
34        1520
36        2607
36        1520
36        8934
38        28
38        15
38        5
38        13
58        2607
58        2607
58        7295
58        1649
58        7295
58        1520
63        2607
63        2607
63        8871
63        7295
63        1520
65        15
65        20
95        1520
95        1520
95        7295
98        1520
98        7295


Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them.

Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to