CODEWITHSUNDEEP
sqloracle-database
Andrii Havrylyak
Andrii Havrylyak

Reputation: 675

Delete duplicate values in the SQL table

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

Upvotes: 0

Views: 103

Answers (3)

Littlefoot
Littlefoot

Reputation: 143103

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

Upvotes: 2

ghovat
ghovat

Reputation: 1043

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

Upvotes: 0

ACAR
ACAR

Reputation: 11

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

Upvotes: 0

Related Questions