I want to select all the orders that have a certain item in it AND have 3 orderlines. So I have to combine a count with a where, but I struggle with it. Here is what I thought of, but it now counts the lines that have this item on it, which is always 1.
select * from ord
where ordnum in (select ol.ordnum
from ord o
join ord_line ol
on o.ordnum = ol.ordnum
where ol.prtnum in ('item_X'))
group by ordnum
having count (ordlin) = 3
The HAVING gets orders with 3 items. The WHERE makes sure that one of the itms is the one you need to include.
Select O.OrdrNum, COUNT( OL.OrdNum ) FROM Ord O INNER JOIN Ord_Line OL ON OL.ordnum = O.ordnum WHERE O.OrdNum IN (SELECT OrdNum FROM Ord_Line WHERE prtnum = 'item_X') GROUP BY O.OrdrNum HAVING COUNT(OL.OrdNum) = 3