CODEWITHSUNDEEP
javasqloracle-databasejoinleft-join
Geek
Geek

Reputation: 3329

Oracle sql join with 3 tables

lk_state table

id  desc
MD  Maryland
FL  Florida
VA  Virginia
NY  NewYork
NJ  NewJersy
CA  California

users table

user_ID Name    Active  State
01      AAA     1       MA  
02      BBB     1       MD
03      CCC     1       CA
04      DDD     1       NY
05      EEE     1       NJ
06      FFF     1       FL
07      GGG     1       FL

users_groups table

id  group
01  10
02  20
03  20
04  30
05  20
06  10
07  20

I want to join the tables which will give me output of states in lk_state table. The condiiotn is display only those states where users belong to role=20

Output should be 

id  desc
MD  Maryland
FL  Florida
NJ  NewJersy
CA  California

I tried joining but it gives me multiple rows one for each users and groups. Please help me with an oracle sql that displays as required.

Upvotes: 1

Views: 50

Answers (3)

Hamid Reza
Hamid Reza

Reputation: 485

Try this:

SELECT DISTINCT s.*
FROM lk_states s, users u, users_groups g
WHERE s.id = u.state
  AND u.user_id = g.id
  AND g.group = 20

Upvotes: 1

VTi
VTi

Reputation: 1319

Have you tried this. Not ideal to keep the keywords as column names 

Select s.* 
from lk_states s
JOIN users u ON u.state = s.id
JOIN users_groups ug ON ug.id = u.user_id
WHERE ug.u_group  = 20

Upvotes: 1

GMB
GMB

Reputation: 222462

You could use exists and a correlated subquery:

select s.*
from lk_states s
where exists (
    select 1
    from users u
    inner join user_groups ug on ug.id = u.user_id
    where u.state = s.id and ug.group = 20
)

Upvotes: 2

Related Questions