chris dangerfield

How do i make this return only distinct records...

SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
dbo.tbl_purchase_orders.date_expected
FROM dbo.tbl_purchase_order_lines INNER JOIN
dbo.tbl_purchase_orders ON
dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
ORDER BY dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_orders.date_expected


help appreciated!!!!
chris

mike john

SELECT distinct
ol.stock_code,
ol.qty_ordered AS stock_ordered,
l.date_expected
FROM dbo.tbl_purchase_order_lines as ol
INNER JOIN dbo.tbl_purchase_orders as l
ON ol.purchase_order_id = l.id
WHERE
(l.delivery_complete = 0) AND
(l.date_expected > GETDATE() - 1)
ORDER BY stock_code, date_expected

ie just add the distinct, the top 100 percent looks effectively redundant anyway, also corelation names (table aliases) make it easier to read and less typing

Mike John