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