Left joining with multiple condition in ORACLE SQL-CodePudding

I have a table:

ser_1	ser_2	ser_3	ser_name
11	12	13	name1
22	44		name2
	55	66	name3
77		78	name4
11	44	66	name5

There is a user table:

user_id	ser_1	ser_2	ser_3
1	11	12	13
2	22	44
3	22	44	55
4	11	12
5		55	66
6	13	55	66
7	77		78
8	77	22	78

And I want to get such a table:

user_id	ser_name
1	name1
2	name2
3	name2
5	name3
6	name3
7	name4
8	name4

For example, user_id = 3 has name2, because it has ser_1 = 22, ser_2 = 44 and ser_3 = 55, 22 and 44 create name2

I tried:

select u.u_id,s.ser_name
  from user u
left join service s  on (u.ser_1 = s.ser_1 and u.ser_2 = s.ser_2 and u.ser_3 = s.ser_3 )
                     or (u.ser_1 = s.ser_1 and u.ser_2 = s.ser_2 and (u.ser_3 is null or 
                                                                       s.ser_3 is null))
                     or (u.ser_1 = s.ser_1 and u.ser_3 = s.ser_3 and (u.ser_2 is null or 
                                                                       s.ser_2 is null))
                     or (u.ser_2 = s.ser_2 and u.ser_3 = s.ser_3 and (u.ser_1 is null or 
                                                                       s.ser_1 is null))

CodePudding user response：

To me, it looks like a join whose conditions are or, not and:

Sample data:

SQL> with
  2  ser (ser_1, ser_2, ser_3, ser_name) as
  3    (select 11  , 12  , 13,   'name1' from dual union all
  4     select 22  , 44  , null, 'name2' from dual union all
  5     select null, 55  , 66  , 'name3' from dual union all
  6     select 77  , null, 78  , 'name4' from dual
  7    ),
  8  tuser (user_id, ser_1, ser_2, ser_3) as
  9    (select 1, 11  , 12  , 13   from dual union all
 10     select 2, 22  , 44  , null from dual union all
 11     select 3, 22  , 44  , 55   from dual union all
 12     select 4, 11  , 12  , null from dual union all
 13     select 5, null, 55  , 66   from dual union all
 14     select 6, 13  , 55  , 66   from dual union all
 15     select 7, 77  , null, 77   from dual union all
 16     select 8, 77  , 22  , 78   from dual
 17    )

Query & result:

 18  select u.user_id, s.ser_name
 19  from tuser u join ser s on u.ser_1 = s.ser_1
 20                          or u.ser_2 = s.ser_2
 21                          or u.ser_3 = s.ser_3
 22  order by u.user_id;

   USER_ID SER_N
---------- -----
         1 name1
         2 name2
         3 name2
         4 name1
         5 name3
         6 name3
         7 name4
         8 name4

8 rows selected.

SQL>

CodePudding user response：

You need an INNER join and not a LEFT join since you want to exclude the users that don't match.
Use these conditions:

SELECT u.user_id, s.ser_name
FROM "user" u INNER JOIN service s
ON  (u.ser_1 = s.ser_1 OR s.ser_1 IS NULL)
AND (u.ser_2 = s.ser_2 OR s.ser_2 IS NULL)
AND (u.ser_3 = s.ser_3 OR s.ser_3 IS NULL)
ORDER BY u.user_id;

See the demo.