> 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=arch...@jab.org
