CODEWITHSUNDEEP
sqloracle-databasejoin
ajay chauhan
ajay chauhan

Reputation: 89

Combination of INNER JOIN and IN :Oracle Sql

I have 4 tables, USER(ID, USER_ID, NAME), USER_2(ID, USER_ID, EMAIL) and USER_CLASS_MAP(ID, USER_ID, CLASS_ID) and CLASS(ID, NAME). USER_CLASS_MAP is basically to map MANY-MANY relation between USER and CLASS.

Need to send all the details of all the users belonging to a particular class. I have non-working code as below- any idea what I might be doing wrong, or is there any other efficient way to achieve the same?

I am referring (SQL Inner-join with 3 tables?) for INNER JOIN over multiple tables, but here the issue is that instead of a single user against the given CLASS_ID, I get a list of USER_ID from USER_CLASS_MAP.

SELECT USER_ID from USER_CLASS_MAP where CLASS_ID=:classID

This is what I currently have:

SELECT USER1.NAME, USER2.EMAIL
INNER JOIN CLASS_USER_MAP as cmap
  ON cmap.USER_ID = USER1.ID
INNER JOIN CLASS_USER_MAP as cmap
  ON cmap.USER_ID = USER2.ID

The problem here is that .ID is basically a list!

EXAMPLE:

USER:
(id1, user1, rob)
(id2, user2, bob)

USER_2:
(id1, user1, [email protected])
(id2, user2, [email protected])

USER_CLASS_MAP:
(id1, user1, class1)
(id2, user2, class1)

CLASS:
(class1, Biology)
(class2, Chemistry)

Given:
Get all User Details for class with classId = class1

Output:
[
  {USER_ID=user1, NAME=rob, [email protected]},
  {USER_ID=user2, NAME=bob, [email protected]}
]

Upvotes: 1

Views: 398

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Your sample data:

create table "USER"(ID, USER_ID, NAME) as (
  select 'id1', 'user1', 'rob' from dual union all
  select 'id2', 'user2', 'bob' from dual
);
create table USER_2(ID, USER_ID, EMAIL) as (
  select 'id1', 'user1', '[email protected]' from dual union all
  select 'id2', 'user2', '[email protected]' from dual
);
create table USER_CLASS_MAP(ID, USER_ID, CLASS_ID) as (
  select 'id1', 'user1', 'class1' from dual union all
  select 'id2', 'user2', 'class1' from dual
);
create table CLASS(ID, NAME) as (
  select 'class1', 'Biology' from dual union all
  select 'class2', 'Chemistry' from dual
);

The query:

select u.user_id, u.name, email
from class c 
       inner join USER_CLASS_MAP uc
         on ( uc.class_id = c.id)
       inner join "USER" u
         on ( uc.user_id = u.user_id)
       inner join USER_2 u2
         on ( u2.user_id = u.user_id)

The result:

user1   rob [email protected]
user2   bob [email protected]

Notice that USER is a reserved word, that's why I used the double quotes; it would be better not to use reserved words to name objects.

Upvotes: 2

Related Questions