List:General Discussion« Previous MessageNext Message »
From:LAMP Date:March 16 2011 12:35am
Subject:Re: Need help with query
View as plain text  
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
>>

Thread
Updating Sql Server DB from MySqlAmador Antonio Cuenca18 Feb
  • Need help with queryLAMP15 Mar
    • Re: Need help with queryhsv23 Mar
Re: Need help with queryLAMP16 Mar
Re: Need help with queryLAMP17 Mar
  • Re: Need help with queryPeter Brawley17 Mar
    • Re: Need help with queryLAMP17 Mar
  • Re: Need help with queryRoy Lyseng19 Mar
Re: Need help with queryLAMP18 Mar
  • Re: Need help with queryhsv18 Mar