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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
