RobNemoo RobNemoo - 4 months ago 8
SQL Question

Select orders with certain item and certain line count

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


Do I need to make 2 selects and combine the results? How can I do that? Thanks in advance.

Answer

The HAVING gets orders with 3 items. The WHERE makes sure that one of the itms is the one you need to include.

HTH.

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
Comments