Count in Query 400 not passing correctly

Question

I Have a table that has order data like this: Table Order_Detail and Item_Master are joined by Item# We want to report on order number, Order_Detail table:

Order#     Item#              

1234       IPhone6 
1234       IPhone5
1234       Battery

join Item_Master:

Item#            Item_type    Desc

IPhone6          Phone        Smartphone
IPhone5          Phone        Smartphone

Now we only want order numbers, that have only one Item-Type = Phone. We are only interested in types Phone. I tried using Query/400 and doing a count on order# which = Phone, and then taking only the counts = 1. But this does bring in some orders that have more than one phone type = Phone, in our example here we would not want this order.

Answer 1

Your question is a little confusing. Are you looking to get a count of order numbers where Item_Type = 'Phone'? If so the follwoing should work for you:

SELECT COUNT(DISTINCT OrderNum) AS OrderNumCount
FROM Order_Detail o
INNER JOIN Item_Master i ON o.ItemNum = o.ItemNum
WHERE Item_type = 'Phone'

Or are you after only the orders that have only one record from the item table linked. If so then you may want:

SELECT o.OrderNum
FROM Order_Detail o
INNER JOIN Item_Master i ON o.ItemNum = o.ItemNum
WHERE Item_type = 'Phone'
GROUP BY o.OrderNum
HAVING COUNT(*) = 1 

Answer 2

You could try something like

SELECT o.OrderNum 
FROM 
  Order_Detail o 
INNER JOIN 
  Item_Master m
On o.ItemNum = m.ItemNum
WHERE m.Item_Type = 'Phone'
GROUP BY o.OrderNum
HAVING COUNT(*) = 1