March 1, 2011 at 11:37 am
create table orders(
oid int,
item varchar (10)
)
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1064')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (3,'1065')
insert into orders (oid, itemid)
values (4,'1063')
I'd like to find only the orders that have ALL the same items and the item is '1063'. so the result would give me order 2 and 4 only. I've tried many grouping and subquery techniques, but I'm kind of stuck. Would appreciate any pointers.
March 1, 2011 at 11:57 am
Try this:
select *
from orders outer_table
where itemid = '1063'
and not exists ( select 1 from orders inner_table
where inner_table.oid = outer_table.oid
and inner_table.itemid <> '1063')
THis may not be the best perfomant code, but i guess it does what is reqd.. I havent tested this though, so please TEST it..
March 1, 2011 at 11:58 am
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1064')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (3,'1065')
insert into orders (oid, itemid)
values (4,'1063')
I'd like to find only the orders that have ALL the same items and the item is '1063'. so the result would give me order 2 and 4 only. I've tried many grouping and subquery techniques, but I'm kind of stuck. Would appreciate any pointers.
A question, how did you decide you only wanted 2 and 4? 1 also has a 1063, but has additional items, so in theory you could display 1, and leave the rest out since they don't have a matching 1064 as well.
Or is the requirement to find records that ONLY have a sale of item 1063?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 1, 2011 at 12:00 pm
Craig Farrell (3/1/2011)
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1063')
insert into orders (oid, itemid)
values (1,'1064')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (2,'1063')
insert into orders (oid, itemid)
values (3,'1065')
insert into orders (oid, itemid)
values (4,'1063')
I'd like to find only the orders that have ALL the same items and the item is '1063'. so the result would give me order 2 and 4 only. I've tried many grouping and subquery techniques, but I'm kind of stuck. Would appreciate any pointers.
A question, how did you decide you only wanted 2 and 4? 1 also has a 1063, but has additional items, so in theory you could display 1, and leave the rest out since they don't have a matching 1064 as well.
Or is the requirement to find records that ONLY have a sale of item 1063?
The requirement is to find records that ONLY have a sale of item 1063. So ONLY orders that have ONLY tem 1063.
March 1, 2011 at 12:11 pm
Craig Farrell (3/1/2011)
A question, how did you decide you only wanted 2 and 4? 1 also has a 1063, but has additional items, so in theory you could display 1, and leave the rest out since they don't have a matching 1064 as well.
Or is the requirement to find records that ONLY have a sale of item 1063?
Craig, i think he wants to find out the OIDs that nave only one item id (1063).
March 1, 2011 at 12:16 pm
riyaz.mohammed (3/1/2011)
The requirement is to find records that ONLY have a sale of item 1063. So ONLY orders that have ONLY tem 1063.
Ah! I misunderstood that the first time, apparently. ColdCoffee's answer is one of your best bets then.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 1, 2011 at 12:22 pm
Will you need to find orders that have the same two (or more) items (and nothing else)? Or just the ones with the same one item? If you are going to be looking for ones with potentially more than one item code, will they need to have the same quantity?
If, for example, order 10 and order 11 both have items 1234 and 2345, one of each on each order, will you ever need to know that?
If orders 12 and 13 have items 3456 and 4567, but 12 has 2 of 3456 and 13 only has 1 of that, will you need to know that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 12:25 pm
ColdCoffee (3/1/2011)
Try this:
select *from orders outer_table
where itemid = '1063'
and not exists ( select 1 from orders inner_table
where inner_table.oid = outer_table.oid
and inner_table.itemid <> '1063')
THis may not be the best perfomant code, but i guess it does what is reqd.. I havent tested this though, so please TEST it..
Thank you kind sir. This did the trick. I had to add a isnull clause in the inner_table.itemid portion to take care of null itemids.
March 1, 2011 at 12:27 pm
The query did not need to be as complex as you have asked. It was just to find orders that have ALL the same of item 1063. ColdCOffee answered my question. Thanks all!
March 1, 2011 at 1:49 pm
Another option
select Oid
from orders
group by oid
having MAX(item) = MIN(item)
and MIN(item) = '1063'
March 2, 2011 at 9:00 am
Dave Ballantyne (3/1/2011)
Another option
select Oidfrom orders
group by oid
having MAX(item) = MIN(item)
and MIN(item) = '1063'
This works but does not handle teh scenario where the item is null. In such a case, we want to exclude that order from the results.
March 2, 2011 at 9:08 am
riyaz.mohammed (3/1/2011)
ColdCoffee (3/1/2011)
Try this:
select *from orders outer_table
where itemid = '1063'
and not exists ( select 1 from orders inner_table
where inner_table.oid = outer_table.oid
and inner_table.itemid <> '1063')
THis may not be the best perfomant code, but i guess it does what is reqd.. I havent tested this though, so please TEST it..
Thank you kind sir. This did the trick. I had to add a isnull clause in the inner_table.itemid portion to take care of null itemids.
You're welcome, Riyaz.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply