Joining 4 tables in Oracle

Question

I cant figure about how to go about this problem. I have done with simple joins but this seems to me complex . Here's what I've got tables

USERS
-------------------
ID | NAME | ROLE_ID
1  | BOB  |    1
2  | PETE |    2
3  | JOS  |    3


USER_ROLES
----------
ID | ROLE 
1  | ADMIN
2  | MANAGER
3  | STAFF

CARS
---------
ID | CAR
1  | BMW
2  | MERCEDES
3  | AUDI

USER_CARS
----------------------
ID | USER_ID | CAR_ID
1  |    1    |   1
2  |    2    |   3
3  |    2    |   2
4  |    3    |   1
5  |    3    |   2

My Expected output

Expected Output
-----------------------------------------------------
ID | NAME | ROLE_ID | ROLE    | BMW | MERCEDES | AUDI
1  | BOB  |    1    | ADMIN   |  1  |    0     |  0
2  | PETE |    2    | MANAGER |  0  |    1     |  1
2  | JOS  |    3    | STAFF   |  1  |    1     |  0

1 in the cars column means that the user has that car, 0 means he does not. What would be the basic logic behind this - using loops or whats the efficient way to query??

Answer 1

You can achieve this using pivot function in Oracle

with users(id, name, role_id) as (
  select 1, 'BOB', 1 from dual union all
  select 2, 'PETE', 2 from dual union all
  select 3, 'JOS', 3 from dual ),
user_roles(id, role) as (
  Select 1, 'ADMIN' From Dual Union All
  Select 2, 'MANAGER' From Dual Union All
  Select 3, 'STAFF' From Dual),
Cars(Id, Car) As (
  Select 1 , 'BMW' From Dual Union All
  Select 2,'MERCEDES' From Dual Union All
  Select 3,'AUDI' From Dual),
User_Cars (Id,User_Id,Car_Id) As (
  select 1, 1, 1 from dual union all
  Select 2, 2, 3 From Dual Union All
  Select 3, 2, 2 From Dual Union All
  Select 4, 3, 1 From Dual Union All
  SELECT 5, 3, 2 FROM DUAL)
----------------
-- End if data preparation
----------------
select * from (
      SELECT u.id, u.name, u.role_id, ur.role, c.car
        FROM USERS U
        JOIN USER_ROLES UR ON UR.ID = U.ROLE_ID
        JOIN USER_CARS UC ON UC.USER_ID = U.ID
        JOIN CARS C ON C.ID = UC.CAR_ID)
PIVOT (COUNT(1) FOR CAR IN ('BMW' AS BMW, 'MERCEDES' AS MERCEDES, 'AUDI' AS AUDI))
order by 1;

output:

| ID | NAME | ROLE_ID |    ROLE | BMW | MERCEDES | AUDI |
|----|------|---------|---------|-----|----------|------|
|  1 |  BOB |       1 |   ADMIN |   1 |        0 |    0 |
|  2 | PETE |       2 | MANAGER |   0 |        1 |    1 |
|  3 |  JOS |       3 |   STAFF |   1 |        1 |    0 |

You Query will be

select * from (
      SELECT u.id, u.name, u.role_id, ur.role, c.car
        FROM USERS U
        JOIN USER_ROLES UR ON UR.ID = U.ROLE_ID
        JOIN USER_CARS UC ON UC.USER_ID = U.ID
        JOIN CARS C ON C.ID = UC.CAR_ID)
PIVOT (COUNT(1) FOR CAR IN ('BMW' AS BMW, 'MERCEDES' AS MERCEDES, 'AUDI' AS AUDI))
order by 1;

Without using pivot, you can write as given below

SELECT u.id, 
       u.name, 
       u.role_id, 
       ur.role, 
       count(decode(c.car, 'BMW', 1, null)) BMW,
       count(decode(c.car, 'MERCEDES', 1, null)) MERCEDES,
       count(decode(c.car, 'AUDI', 1, null)) AUDI
  FROM USERS U
  JOIN USER_ROLES UR ON UR.ID = U.ROLE_ID
  JOIN USER_CARS UC ON UC.USER_ID = U.ID
  JOIN CARS C ON C.ID = UC.CAR_ID
group by u.id, 
         u.name, 
         u.role_id, 
         ur.role
order by 1;