List:General Discussion« Previous MessageNext Message »
From:LAMP Date:March 17 2011 7:36pm
Subject:Re: Need help with query
View as plain text  
First I was thinking there is function IN ALL or something like that,  
since there are functions IN and EXISTS. And I would be able to make a  
query something like this
     select distinct org_id, item_id
     from orders
     where item_id in all (34, 36, 58, 63)
  order by org_id asc

But, there isn't any. :-(


The correct query is

select r.org_id
from
(
      select distinct a.org_id, a.item_id
      from orders a
      where a.item_id in (34, 36, 58, 63)
      order by a.org_id asc
) r
group by r.org_id
having count(*) >= 4







On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote:

> > What I need is a list of orgs they bought all of items 34, 36, 58,  
> 63. every of them.
>
> Some solutions under "What else did buyers of X buy" at
> http://www.artfulsoftware.com/infotree/queries.php 
> .
>
> PB
>
> ---
>
> On 3/17/2011 12:00 PM, LAMP wrote:
>> Yes, that was my question. Though, since English is not my first  
>> language, let me try to post it again:
>>
>> There is a list of all orgs and items org bough, from table called  
>> orders
>>
>> 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
>>
>>
>> select org_id from orders where item_id in (34. 36. 58. 63) will  
>> give me a result
>>
>> 5
>> 13
>> 15
>> 28
>> 1520
>> 1649
>> 2607
>> 7295
>> 8871
>> 8934
>>
>> This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
>> 63). Agree?
>>
>> What I need is a list of orgs they bought all of items 34, 36, 58,  
>> 63. every of them. Result should be only orgs 2607 and 1520.
>>
>> I hope it's more clear now.
>>
>>
>>
>> On Mar 15, 2011, at 10:47 PM, Rhino wrote:
>>
>>>
>>> Your original question said: "Need to select all (distinct) org_id  
>>> they have item_id 34, 36, 58 and 63. All of them, not only some of  
>>> them. "
>>>
>>> That's the question I answered with my suggested query.
>>>
>>> It sounds like that is not what you meant after all but I'm not  
>>> sure what you DO want with your query. Why are 2607 and 1520 the  
>>> only right answers?
>>
>> Because they are. I look at the database and "manually" found the  
>> result I have to get. What's wrong with my statement?
>>
>>> Based on your own query, it looks like you only want an org_id for  
>>> item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
>>> that org_id amongst the desired item_ids
>>
>> actually, there is mistake in my query, it should say "having  
>> count(org_id) >= 4"
>> and, yes, that's what I want. I can get the correct list using the  
>> query I posted but I was hoping there is BETTER way.
>>
>>> but that wasn't in your statement of the problem. So please  
>>> clarify EXACTLY what you want. Giving an incomplete or  
>>> contradictory description of you want only wastes both your time  
>>> and mine.
>>
>> As I stated earlier, English is not my first language and I was  
>> trying to do my best. Sorry for confusing you.
>>
>>
>>>
>>> -- 
>>> Rhino
>>>
>>> On 2011-03-15 20:35, LAMP wrote:
>>>>
>>>> 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