Filter related records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Filter related records

Author

Topic

arshadjehan
Starting Member

2 Posts

Posted - 2011-01-21 : 02:25:52

I have a scenario for retrieving parent child records:

I have two table Orders and OrderDetails:

Sample data is:

Orders:
OrderId ODate Value Name
1111 05/01/2011 25 Mike
2222 09/01/2011 56 Peter
3333 11/01/2011 09 Robert

OrderDetails:

OrderId ItemCode Qty Price
1111 AAAA 2 12.5
2222 AAAA 2 12.5
2222 BBBB 1 31
3333 CCCC 1 9

What I want is to retrieve all Order containing Items with

ItemCode "AAAA" AND "BBBB".

I used the query with IN clause as following:
SELECT Orders.OrderId, ODate, Name, Value FROM Orders INNER JOIN

OrderDetails ON Orders.OrderId=OrderDetails.orderId WHERE ItemCode

IN('AAAA','BBBB')

But it works like OR operator and returns two orders 1111 and

2222, although only orderNo 1111 contains both items.

How can I perform AND operation in this case.

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-21 : 04:40:08

quote:
Originally posted by arshadjehan

I have a scenario for retrieving parent child records:

I have two table Orders and OrderDetails:

Sample data is:

Orders:
OrderId ODate Value Name
1111 05/01/2011 25 Mike
2222 09/01/2011 56 Peter
3333 11/01/2011 09 Robert

OrderDetails:

OrderId ItemCode Qty Price
1111 AAAA 2 12.5
2222 AAAA 2 12.5
2222 BBBB 1 31
3333 CCCC 1 9

What I want is to retrieve all Order containing Items with

ItemCode "AAAA" AND "BBBB".

I used the query with IN clause as following:
SELECT Orders.OrderId, ODate, Name, Value FROM Orders INNER JOIN

OrderDetails ON Orders.OrderId=OrderDetails.orderId WHERE ItemCode

IN('AAAA','BBBB')

But it works like OR operator and returns two orders 1111 and

2222, although only orderNo 1111 contains both items.

How can I perform AND operation in this case.

create table #order(OrderId int,ODate datetime,Value int,Name varchar(10))
insert into #order
select 1111,'05/01/2011',25,'Mike' union all
select 2222,'09/01/2011',56,'Peter' union all
select 3333,'11/01/2011',09,'Robert'

create table #orderDetails(OrderId int,ItemCode varchar(10),Qty int,Price money )

insert into #orderDetails
select 1111,'AAAA',2,12.5 union all
select 2222,'AAAA',2,12.5 union all
select 2222,'BBBB',1,31 union all
select 3333,'CCCC',1,9

select OrderId, ODate, Name, Value FROM #Order
where Orderid in (
select OrderId
from #OrderDetails
where ItemCode in ('AAAA','BBBB')
group by OrderId
having count(ItemCode) > 1
)

sat

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-21 : 04:49:48

SELECT O.OrderId, ODate, Name, Value FROM #Order o
JOIN (SELECT OrderId AS orderId1
FROM #OrderDetails
WHERE ItemCode IN ('AAAA','BBBB')
GROUP BY OrderId
HAVING COUNT(ItemCode) > 1) AS os ON O.OrderId=Os.orderId1

sat

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-01-21 : 06:47:46

simply u can use
select * from #Order where orderid in (select orderid from #OrderDetails where ItemCode in ('AAAA','BBBB'))

arshadjehan
Starting Member

2 Posts

Posted - 2011-01-21 : 08:47:48

Thanks sathishmangunuri
This is what I wanted to do.

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-23 : 23:18:03

welcome