List:General Discussion« Previous MessageNext Message »
From:LAMP Date:March 18 2011 1:49pm
Subject:Re: Need help with query
View as plain text  
On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote:

> Indeed, I don't thing there is.
>
> Just be sure that each record has an unique combination of org_id  
> and item_id, otherwise you might end up with an org_id that, for  
> example, references 4 times item_id 34 in 4 different records, but  
> no other item_ids. This is obvisouly not what you want.
>
> Geert-Jan

Correct. That's why I use "select distinct org_id, item_id" in sub- 
query.

Is here anybody from mysql development team, to suggest to build IN  
ALL function?
:-)




>
> 2011/3/17 LAMP <lamp@stripped>
> 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
>
>
>
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


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