CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Piekar
Piekar

Reputation: 21

Oracle Db Group By Two Columns

I want to group by 2 columns my first table is:

Users

ID Name Surname
1  joe   New
2  Ala   Bla
3  Cat   Kra
4  Zoo   Fles
5  Kat   Glo

Work:

ID BOSSID1 BOSSID2
1  1       2
2  2       3
3  2       1
4  2       5
5  3       5

And I want to see all Bosses in table "Work".

for example:

1 joe   New
2 Ala   Bla
3 Cat   Kra
5 Kat   Glo

Upvotes: 0

Views: 71

Answers (3)

radar
radar

Reputation: 13425

you can do union and then join with users table

SELECT CONCAT_WS(' ', U.Name, U.Surname)
FROM Users U
JOIN
(SELECT BOSSID1 as BossID
FROM WORK
UNION
SELECT BOSSID2 as BossID
FROM WORK
) T
on U.ID = T.BossID

Upvotes: 1

Aramillo
Aramillo

Reputation: 3226

Try this:

SELECT DISTINCT u.* FROM USERS u INNER JOIN WORK w 
ON (u.id = w.bossid1 OR u.id = w.bossid2)

Upvotes: 1

Riad
Riad

Reputation: 3860

Try this:

select id, concat(name, ' ', surname) from
(
    select id, name, surname from users 
    where id in 
    (
      select distinct bossid1 from work

    ) OR id in 
    (
      select distinct bossid2 from work
    )
)

Upvotes: 1

Related Questions