Delete duplicate values in the SQL table

Question

I need help, I have a request:

 select 
       df.ID, 
       df.login as name,
       ev.NAME as organizations,
       cv.role_names as role
  from ORGANIZATIONS_USERS lo
       LEFT JOIN users_cdf ON df.ID = lo.USER_ID
       LEFT JOIN ORGANIZATIONS ev ON lo.ORGANIZATION_ID = ev.id
       LEFT JOIN APEX_APPL_ACL_USERS cv ON df.login = cv.USER_NAME

My answers:

ID   NAME   ORGANIZATIONS   ROLE  
1    John   Home            1
2    Sam    Work3           2
2    Sam    Work2           2
2    Sam    Work1           2
5    Tom    TOV             3

I want not to repeat the entries where the same name

ID   NAME   ORGANIZATIONS   ROLE  
1    John   Home            1
2    Sam    Work3           2
5    Tom    TOV             3

You need to delete duplicate values ? You wrote : "I want not to repeat the entries where the same name", do you mean DELETE and not REPEAT ? Or it is only the caption of this question that is wrong? — VBoka, Oct 22 '19 at 12:46

score 2 · Answer 1 · answered Oct 22 '19 at 12:24

Aggregate what causes problems (organization), group by the rest:

  SELECT df.ID,
         df.login AS name,
         MAX (ev.NAME) AS organizations,                              --> this
         CV.role_names AS role
    FROM ORGANIZATIONS_USERS lo
         LEFT JOIN users_c df ON df.ID = lo.USER_ID
         LEFT JOIN ORGANIZATIONS ev ON lo.ORGANIZATION_ID = ev.id
         LEFT JOIN APEX_APPL_ACL_USERS CV ON df.login = CV.USER_NAME
GROUP BY df.id, df.login, CV.role_names                               --> this

score 0 · Answer 2 · answered Oct 22 '19 at 12:23

select DISTINCT df.ID, 
df.login as name,
  (SELECT ev.NAME FROM  ORGANIZATIONS ev WHERE lo.ORGANIZATION_ID = ev.id AND
   ROWNUM <2 ) as organizations,
 cv.role_names as role
 from ORGANIZATIONS_USERS  lo 
 LEFT JOIN users_c  df  ON df.ID = lo.USER_ID
 LEFT JOIN APEX_APPL_ACL_USERS cv ON df.login = cv.USER_NAME

score 0 · Answer 3 · answered Oct 22 '19 at 12:24

You can use group by:

select df.ID, 
df.login as name,
ev.NAME as organizations,
cv.role_names as role
from ORGANIZATIONS_USERS  lo
   LEFT JOIN users_c      df         
ON df.ID = lo.USER_ID
  LEFT JOIN ORGANIZATIONS      ev  
ON lo.ORGANIZATION_ID = ev.id
 LEFT JOIN APEX_APPL_ACL_USERS cv
ON df.login = cv.USER_NAME
GROUP BY df.login, df.id

You can take a look here for an example: Left Join without duplicate rows from left table

Please note in the answer you have to insert what you want to group on. I assume dg.login

Delete duplicate values in the SQL table

3 Answers3