Getting duplicates in a sql query

Question

Trying to create a dashboard using the following query -

select a.distinct_id, 
       trunc(a.dte) Transaction_Date, 
       a.dte, 
       a.activity, 
       a.part, 
       a.loading, 
       a.user_name, 
       a.device, 
       a.fromloc, 
       a.toloc, 
       a.to_area, 
       b.NAME||' '||b.Surname Name, 
       a.qty, 
       DECODE(d.workarea, 'Tables','Wood','Metal')Material
from table a,  table b, table c, table d
where (a.toloc = d.storage 
  and a.part = c.part and c.region = 'Country1' 
  and a.dte > trunc(sysdate) - '9' 
  and a.region = 'Country1' 
  and a.activity = 'IDENTIFY' 
  and a.user_name = b.user_name 
  and d.maxarea <> 0 )

When I remove the last constraint from the where statement (ie d.maxarea <> 0) the output does not show repeats but when I add that in the query again, it shows repetitive records with the same distinct_id.

Don't use "please xxx" in the question title. [here](https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql) is explained why you can have duplicates when doind implicit JOIN operation. — pptaszni, Sep 12 '18 at 15:47
Hello, and welcome to Stack Overflow. Please read https://stackoverflow.com/help/how-to-ask - you will get better responses. For SQL questions, it really helps if you show a schema, sample data, actual and expected results, ideally as a SQLFiddle. — Neville Kuyt, Sep 12 '18 at 15:57

score 3 · Answer 1 · answered Sep 12 '18 at 15:27

Your query should be written like this:

select a.distinct_id, trunc(a.dte) as Transaction_Date, a.dte, a.activity, a.part, a.loading, a.user_name, a.device, a.fromloc, a.toloc, a.to_area,
       (b.NAME||' '||b.Surname Name), a.qty,
       (case when d.workarea = 'Tables' then 'Wood' else 'Metal' end) as Material   
from a join
     b
     on a.user_name = b.user_name join
     c
     on a.part = c.part join
     d
     on a.toloc = d.storage
where c.region = 'Country1' and
      a.dte > trunc(sysdate) - interval '9' day and
      a.region = 'Country1' and
      a.activity = 'IDENTIFY' and
      d.maxarea <> 0;

I'm not sure what your actual problem is, but you should learn the proper way to write a query:

Never use commas in the FROM clause. Always use proper, explicit, standard join syntax.
decode() is database-specific. The standard logic is case.

score 0 · Answer 2 · answered Sep 12 '18 at 15:29

If you want distinct rows, then you need to add a DISTINCT to the code as in

  SELECT DISTINCT a.distinct_id, trunc(a.dte) Transaction_Date, a.dte, a.activity, a.part, 
  a.loading, a.user_name, a.device, a.fromloc, a.toloc, a.to_area, b.NAME||' '||b.Surname 
  Name, a.qty, DECODE(d.workarea, 'Tables','Wood','Metal')Material

  from table a,  table b, table c, table d

  where 
  (a.toloc = d.storage and a.part = c.part and c.region = 'Country1' and a.dte > 
   trunc(sysdate) - '9' and a.region = 'Country1' and a.activity = 'IDENTIFY' and 
  a.user_name = b.user_name and d.maxarea <> 0 )

I would however suggest using joins from SQL92, rather than SQL89 format for ease of reading

Getting duplicates in a sql query

2 Answers2