SQL Query - pulling a value from a subset

[Prognewb]

I don't use SQL much these days, so my query writing skills have deteriorated... I've googled and looked around the net but haven't found a specific answer for my issue.

I have two tables, one that holds header information for sales orders, and the other that holds line information for those orders. They link through the order number. I am trying to write a query that will pull the order number for orders that have two specific items on them only.

The 'or' statement alone pulls all orders that have both items, as well as orders with just one or the other on them, and I want just the orders that have both. What I have so far:

select
header.ord_no,
line.item_no
from header
join line
on header.ord_no = line.ord_no
where line.item_no = 1
or line.item_no = 2

I know it's the 'or' statement that is the problem, but I'm not quite sure what the proper command is for this specific instance. Any help is greatly appreciated. Thanks.

 

[Crowley16]

you'll need to use another join to the line table and filter one with item_no = 1 and filter the other with item_no = 2

something like:

select *
from
Headder h inner join
line l1 on h.id = l1.id inner join
line l2 on h.id = l2.id
where
l1.id = 1
and l2.id = 2

--------------------
Procrastinate Now!

 

[bborissov]

select header.ord_no,
       line.item_no
from header
INNER JOIN (SELECT ord_no, COUNT(*) AS Cnt
                   FROM Line
            WHERE line.item_no IN (1,2)
            GROUP BY ord_No
            HAVING COUNT(*) = 2) LineTmp
      ON header.ord_no = LineTmp.ord_no
INNER JOIN Line ON header.ord_no = line.ord_no

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Prognewb]

Thank you, I hadn't thought of using an inner join, that did the trick. I appreciate the help!

 

[PWise]

bborissov:

Your query will return orders with two lines for item 1

try

Select header.* 
from header
inner join 
(Select distinct orderno
from lines item1
inner join lines item2
on item1.orderno=item2.orderno
and item1.item=1
and item2.item=2)lineitems
on lineitems.orderno = header.orderno