Updating Sql Server DB from MySql

--000e0cd32ada55bdc5049c90cca7
Content-Type: text/plain; charset=ISO-8859-1

Hi all,

I'm working for a company which has a administrative system under C# and
SqlServer and they have a payment system under PHP + MySql. The want I
create a trigger to update the SqlServer DB(Payments table) automatically
when a record is inserted in the MySql DB.

I've heard about LinkedServer on SqlServer, there is a equivalent in MySql
to query SQLServer.

I'll really appreciate your help.

Regards,
--
TSU. Amador Cuenca

--000e0cd32ada55bdc5049c90cca7--

Need help with query

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=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

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=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

--Apple-Mail-4--48522826
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

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
>>>>
>>
>>


--Apple-Mail-4--48522826--

Re: Need help with query

> 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=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

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=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

--Apple-Mail-7-26438273
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit


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
> 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=gbrits@gmail.com
>
>


--Apple-Mail-7-26438273--

Re: Need help with query

>>>> 2011/03/18 08:49 -0500, LAMP >>>>
Is here anybody from mysql development team, to suggest to build IN
ALL function?
<<<<<<<<
There is a problem here: the basic operation is on the record, each record by each record, all by itself. The solution to your problem entails acting on more distinct records until enough have been encountered.

If you imagine the table input to a program that checks for hits, you will see the problem. The program reads its input, for every number of the four that you want matched it holds on to its mate until that mate is matched with all four of the chosen. It is a global condition, and SQL works one record at a time. Global conditions are detected only through the summary functions.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

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=gcdmg-mysql-2@m.gmane.org

Re: Need help with query

>>>> 2011/03/15 17:51 -0500, LAMP >>>>
Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
item_id int,
org_id int,
) ENGINE=MyISAM

.....

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
<<<<<<<<
I now noticed the aggregate function GROUP_CONCAT:

select org_id,GROUP_CONCAT(DISTINCT item_id, ORDER BY item_id) AS itemset
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having itemset = '34,36,58,63'


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org