Help forming a specific select query
-
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
-
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'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thank you very much. that worked perfectly.
-
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.
-
A boxer, perhaps?
OK, good point.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply