SQL Finding Orders belonging to a product category

Im trying to find all orders in the database where the product belongs to a product category. But the category is stored in the Orderline table.

My table structure is roughly:

Order Table

ID
Date

OrderLine Table

ID
Product_ID
ProductCatego开发者_JS百科ry_ID
Quantity

Product Category Table

ID
Name

My sql looks like:

Select 
 o.Id,
    o.Date,
    pf.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id

WHERE 
 pc.ID in ('1000','1001', '1002')

But I suffer from multiple lines per order when an order has multiple orderlines belonging to the same category. As I only want a summary, I just want one row per order.

Use the DISTINCT clause:

Select DISTINCT
    o.Id,
    o.Date,
    pc.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id
WHERE 
 pc.ID in ('1000','1001', '1002')

This will ensure that only distinct rows (rows that are different) are returned.

You also seem to have a syntax error in your SELECT clause (did you mean pc.Name instead of pf.Name?).

Use the GROUP BY statement to group multiple rows together using the specified columns.

Select 
 o.Id,
    o.Date,
    pc.Name,
From Order o
 JOIN OrderLine ol on o.Id = ol.Order_Id
 JOIN ProductCategory pc on ol.ProductCategory_Id = pc.Id
WHERE 
 pc.ID in ('1000','1001', '1002')
GROUP BY o.id, o.date, pc.Name