Using 'in' in Join
I have two choices a and b and I join them like this:
select * from
(
select n.id_b || ',' || s.id_b ids, n.name, s.surname
from names n,
surnames s where n.id_a = s.id_a
) a
left join
(
select sn.id, sn.second_name
) b on b.id in (a.ids)
in this case the connection doesn't work :( The problem is b.id in (a.ids) . But why, if it looks like 12 in (12,24) and no result :(
a source to share
It won't work because it (12,24)
is a single discrete string, not a comma-separated bunch of numbers. It is clear that 12 != '(12,24)'
, therefore, no results are returned.
change
I missed the outer join in your requested request. So you must return something even though there is no join between the two tables. Here's some test data:
SQL> select * from names
2 /
ID_A ID_B NAME
---------- ---------- ----------
1 12 SAM
SQL> select * from surnames
2 /
ID_A ID_B SURNAME
---------- ---------- ----------
1 24 I-AM
SQL> select * from whatever
2 /
ID SECOND_NAM
---------- ----------
24 I AM SAM
SQL>
My query is similar to yours, except that I used it b.id
as a string because a.ids
is a string. If I don't, the request ends with ORA-1722: invalid number
.
SQL> select * from
2 (
3 select n.id_b || ',' || s.id_b ids, n.name, s.surname
4 from names n,
5 surnames s
6 where n.id_a = s.id_a
7 ) a
8 left join
9 (
10 select sn.id, sn.second_name
11 from whatever sn
12 ) b on to_char(b.id) in (a.ids)
13 /
IDS NAME SURNAME ID SECOND_NAM
---------- ---------- ---------- ---------- ----------
12,24 SAM I-AM
SQL>
As you can see, it returns values from the left query and nothing from the right, for the reason I gave above.
If you want to get something from both requests based on partial ID matching, you need to do this:
SQL> select a.ids
2 , a.name
3 , a.surname
4 , b.id
5 , b.second_name
6 from
7 (
8 select n.id_b || ',' || s.id_b ids
9 , n.name
10 , s.surname
11 , n.id_b as n_id_b
12 , s.id_b as s_id_b
13 from names n,
14 surnames s
15 where n.id_a = s.id_a
16 ) a
17 left join
18 (
19 select sn.id, sn.second_name
20 from whatever sn
21 ) b on (b.id = a.n_id_b or b.id = a.s_id_b )
22 /
IDS NAME SURNAME ID SECOND_NAM
---------- ---------- ---------- ---------- ----------
12,24 SAM I-AM 24 I AM SAM
SQL>
If you take this latter approach, you might consider turning the outer join into an inner join. Puts you on the exact business rule.
a source to share