List:General Discussion« Previous MessageNext Message »
From:Roy Lyseng Date:March 19 2011 3:59pm
Subject:Re: Need help with query
View as plain text  
Hi!

I think that the query that you have proposed is the best possible for the problem.

However, if there are duplicates in the orders table, then
   HAVING COUNT(item_id) = 4
should be replaced with
   HAVING COUNT(DISTINCT item_id) = 4

(I assume that you meant item_id and not org_id in the COUNT function).

Thanks,
Roy

On 17.03.11 18.00, 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