December 19, 2005 at 8:04 am
Hi everyone,
At my company are looking to track whenever certain items are sold together. In the example below, we are looking to find all of the transaction numbers where a monitor, keyboard, and a mouse are sold on the same transaction. I replaced the specific itemid's with generics to make it easier to read. I do not know how to form the query I need to search for this. Can anyone please help me with this.
storeid transactionnumber itemid price quantity
1071111 1 hardrive 65.00 1
1081111 2 monitor 106.00 2
1031111 3 keyboard 7.00 1
1031111 3 mouse 7.00 1
1031111 3 monitor 75.00 1
1021111 4 laptop 899.00 1
1051111 5 keyboard 7.00 1
1051111 5 mouse 7.00 1
...
the query needs to return something like this
select all transactionnumber's where a keyboard, mouse, and monitor where sold on the same transaction
Thanks very much for the help
December 19, 2005 at 8:48 am
Here's one that should work:
select t.transactionnumber
from tbl t
join (select t.transactionnumber from tbl t where t.itemid = 'mouse') t1
on t.transactionnumber = t1.transactionnumber
join (select t.transactionnumber from tbl t where t.itemid = 'monitor') t2
on t.transactionnumber = t2.transactionnumber
where t.itemid = 'keyboard'
December 19, 2005 at 9:41 am
Thank you very much. that worked perfectly.
December 19, 2005 at 12:38 pm
Adding a "distinct" to the query might be helpful in case a person bought more than 1 item of the same type on a transaction. Say a person buys 2 Keyboard, 1 monitor and 1 mouse.
December 20, 2005 at 2:30 am
A boxer, perhaps?
OK, good point.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy