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
>>
