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
– VBoka, Commented Oct 22, 2019 at 12:46

Littlefoot · Accepted Answer · 2019-10-22 12:24:40Z

2

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

answered Oct 22, 2019 at 12:24

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ACAR · Accepted Answer · 2019-10-22 12:23:29Z

0

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

answered Oct 22, 2019 at 12:23

ACAR

112 bronze badges

Comments

ghovat · Accepted Answer · 2019-10-22 12:24:26Z

0

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

answered Oct 22, 2019 at 12:24

ghovat

1,0431 gold badge14 silver badges38 bronze badges

Collectives™ on Stack Overflow

Delete duplicate values in the SQL table

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related