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
