Combination of INNER JOIN and IN :Oracle Sql

Question

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, rob@something.something)
(id2, user2, bob@something.something)

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=rob@something.something},
  {USER_ID=user2, NAME=bob, EMAIL=bob@something.something}
]

This would be remarkably improved if you added some *sample data* for each table and your *expected results*. — Damien_The_Unbeliever, Jan 12 '17 at 13:44
Add some sample table data and the expected result. (As well formatted text.) — jarlh, Jan 12 '17 at 13:44
Basically I get a list of USER_IDs from running first query (since its a many-many mapping), so the ID in the second query has to be from this list. — ajay chauhan, Jan 12 '17 at 13:47
Still not clear. Please try to build a small example with some sample data for your tables, then post the result you need to get from your query, even explaining the logic behind the result. — Aleksej, Jan 12 '17 at 13:54

Aleksej · Accepted Answer · 2017-01-12T14:18:14.297

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', 'rob@something.something' from dual union all
  select 'id2', 'user2', 'bob@something.something' 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 rob@something.something
user2   bob bob@something.something

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.

The table names are different from the ones given in the example, they're much bigger- so gave an overtly simplified version. But thanks for pointing it out! — ajay chauhan, Jan 13 '17 at 05:02

Combination of INNER JOIN and IN :Oracle Sql

1 Answers1