SQL - Duplicates when Querying 3 Tables

Question

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.

Change `SELECT` to `SELECT DISTINCT`. Possible duplicate of [How to select unique records by SQL](http://stackoverflow.com/a/1641732/2026606). — Tyler Roper, Oct 19 '16 at 19:31
Why would you choose an archaic syntax when you seem to know that `JOIN` is the better way to express -- well, joins -- between tables. — Gordon Linoff, Oct 19 '16 at 19:34

score 0 · Accepted Answer · answered Oct 19 '16 at 19:36

0

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

answered Oct 19 '16 at 19:36

Sonali

8
2

1

:- what about rest of columns – Ritesh Patel Oct 19 '16 at 19:44
Group by Only eliminates the duplicate data, But according to these other items can be fetched. U hv to write the Query as before, only where part is to changed. – Sonali Oct 20 '16 at 07:11
After my initial Select Statement above here are the changes that pulled the data I was trying to pull. FROM DSLLC.dbo.IV00101 IV00101, DSLLC.dbo.IV00102 IV00102, DSLLC.dbo.ItmPrice ItmPrice WHERE IV00101.ITEMNMBR = IV00102.ITEMNMBR AND IV00102.ITEMNMBR = ItmPrice.ITEMNMBR AND IV00102.LOCNCODE = '' Group by IV00101.ITEMNMBR, IV00101.ITEMDESC, ItmPrice.STNDCOST, ItmPrice.DS_Margin, IV00101.CURRCOST, IV00102.LSORDQTY, IV00102.LSRCPTDT, ItmPrice.MODIFDT, ItmPrice.MDFUSRID ORDER BY IV00101.ITEMNMBR – zungerman5 Oct 20 '16 at 17:26

SQL - Duplicates when Querying 3 Tables

1 Answers1