zungerman5 zungerman5 - 1 month ago 7
SQL Question

SQL - Duplicates when Querying 3 Tables

I have a pretty simple query that pulls data from 3 tables. I decided to use From and Where Clauses to Select what I want instead of Join but when I run the query it pulls duplicate data. DISTINCT was tried as well but it still pulled duplicate data.

Here is the Query -

SELECT
IV00101.ITEMNMBR,
IV00101.ITEMDESC,
ItmPrice.STNDCOST,
ItmPrice.DS_Margin,
IV00101.CURRCOST,
IV00102.LSORDQTY,
IV00102.LSRCPTDT,
ItmPrice.MODIFDT,
ItmPrice.MDFUSRID
FROM
DSLLC.dbo.IV00101 IV00101,
DSLLC.dbo.IV00102 IV00102,
DSLLC.dbo.ItmPrice ItmPrice
WHERE
IV00101.ITEMNMBR = IV00102.ITEMNMBR AND
IV00101.ITEMNMBR = ItmPrice.ITEMNMBR AND
IV00102.ITEMNMBR = ItmPrice.ITEMNMBR
ORDER BY
IV00101.ITEMNMBR


A small sample of the result can be seen here.

Answer

Try this : WHERE IV00101.ITEMNMBR = IV00102.ITEMNMBR AND IV00102.ITEMNMBR = ItmPrice.ITEMNMBR group by IV00101.ITEMNMBR ORDER BY IV00101.ITEMNMBR