How to avoid duplicates between two tables on using join?

Question

I have two tables work_table and progress_table.

work_table has following columns:

id[primary key],
department,
dept_name,
dept_code,
created_time,
updated_time

progress_table has following columns:

id[primary key],
project_id,
progress,
progress_date

I need only the last updated progress value to be updated in the table now am getting duplicates.

Here is the tried code:

select
  row_number() over (order by a.dept_code asc) AS sno, 
  a.dept_name,
  b.project_id,
  p.physical_progress,
  DATE(b.updated_time) as updated_date,
  b.created_time
from 
  masters.dept_users as a,
  work_table as b
LEFT JOIN
  progress as p on b.id = p.project_id
order by
  a.dept_name asc

It shows the duplicate values for progress with the same id how to resolve it?[the progress values are integer whose values are feed to the form]

Do you need to use `SELECT DISTINCT`? You mention `UPDATE`, is this a sub query for an update query? — Timothy Alexis Vass, Jun 30 '21 at 17:00
If you can add sample input records and ouput that would help. Based on the initial understanding, you can do 1,row_number() over (partition by id order by updated_time desc) and then 2, pick records where rownum=1(this step is missing in your query) — linusRian, Jun 30 '21 at 17:01

MatBailie · Answer 1 · 2021-06-30T17:07:59.850

Having reformatted your query, some things become clear...

You've mixed , and JOIN syntax (why!?)
You start with the masters.dept_users table, but don't mention it in your description
You have no join predicate between dept_users and work_table
You calculate an sno, but have no partition by and never use it
Your query includes columns not mentioned in the table descriptions above

And to top it off, you use meaningless aliases like a and b? Please for the love of other, and your future self (who will try to read this one day) make the aliases meaningful in Some way.

You possibly want something like...

WITH
  sorted_progress AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY project_id
          ORDER BY progress_date DESC -- This may need to be updated_time, your question is very unclear
    )
      AS seq_num
  FROM
    progress
)
SELECT
  <whatever>
FROM
  masters.dept_users   AS u
INNER JOIN
  work_table           AS w
    ON  w.user_id = u.id  -- This is a GUESS, but you need to do SOMETHING here
LEFT JOIN
  sorted_progress      AS p
    ON  p.project_id = w.id  -- Even this looks suspect, are you SURE that w.id is the project_id?
    AND p.seq_num    = 1

That at least shows how to get that latest progress record (p.seq_num = 1), but whether the other joins are correct is something you'll have to double (and triple) check for yourself.

How to avoid duplicates between two tables on using join?

1 Answers1