CODEWITHSUNDEEP
sqloracle-database
kyooryu
kyooryu

Reputation: 1509

Join 2 tables on different columns based on condition

I have two tables, tbl and tbl2 I have to join. Columns of tbl are irrelevant. Here is the tructure of tbl2:

CREATE TABLE tbl2 (
a VARCHAR(10)
, b VARCHAR(10)
, c VARCHAR(10)
, d VARCHAR(10)
, e VARCHAR(10)
);

What I need to do is apply the following join:

I have been thinking what is the best way to achieve the following. tbl2 can be modified since it is only used for the purpose of this query.

Do you have any ideas what is the most efficient way of achieving this?

EDIT:

By 'having the same a, b and c' I meant something like this:

SELECT a, b, c FROM tbl2 GROUP BY a, b ,c HAVING COUNT(*) > 1

Upvotes: 0

Views: 6586

Answers (2)

chetan
chetan

Reputation: 2886

put these three queries in if else condition. If you want them all then UNION these three.

select * 
from tbl t1 left outer join tbl2 t2
on (t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d)
where t2.a=t2.b and t2.b=t2.c;

select * 
from tbl t1 left outer join tbl2 t2
on (t1.a=t2.a and t1.b=t2.b and t1.c=t2.c )
where t2.a=t2.b and t2.b<>t2.c;

select * 
from tbl t1 left outer join tbl2 t2
on (t1.a=t2.a and t1.b=t2.b)
where t2.a<>t2.b and t2.b<>t2.c;

or try this:

select * 
from tbl t1 left outer join tbl2 t2
on (
(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t2.a=t2.b and t2.b=t2.c)
     or
(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t2.a=t2.b and t2.b<>t2.c)
     or
(t1.a=t2.a and t1.b=t2.b and t2.a<>t2.b and t2.b<>t2.c) );

Upvotes: 0

the_slk
the_slk

Reputation: 2182

WITH tab_a AS
(
        SELECT  t2.a
        ,       t2.b
        ,       t2.c
        ,       t2.d
        ,       t2.e
        ,       CASE WHEN t1.c = t2.c THEN 1 ELSE 0 END +
                CASE WHEN t1.d = t2.d THEN 1 ELSE 0 END AS other_two
        FROM    tbl t1
        LEFT    JOIN tbl2 t2
        ON      t1.a = t2.a
        AND     t1.b = t2.b
)
SELECT  a
,       b
,       c
,       d
,       e
FROM    tab_a
WHERE   other_two = (SELECT MAX(other_two) FROM tab_a);

Upvotes: 1

Related Questions