Hi!
First of all thanks for taking time to read and answer my question.
So, I got two set of rows which needs to be joined. In some cases it should be outer left join, but in rest it should be inner.
Is it possible make such query?
Small sample with input (table_1, table_2) and desired output (t_result_set)
--source table_1
CREATE TABLE table_1 (id_1 NUMBER, value_1 VARCHAR2(10));
INSERT INTO table_1 VALUES (100, 'Value1');
INSERT INTO table_1 VALUES (100, 'Value2');
INSERT INTO table_1 VALUES (100, 'Value3');
INSERT INTO table_1 VALUES (200, 'Value1');
INSERT INTO table_1 VALUES (200, 'Value2');
INSERT INTO table_1 VALUES (200, 'Value3');
--table which should be joined
CREATE TABLE table_2 (id_1 NUMBER, value_1 VARCHAR2(10));
INSERT INTO table_2 VALUES (100, 'Value2');
--table with result set
CREATE TABLE t_result_set (t1_id1 NUMBER, t1_value_1 VARCHAR2(10), t2_id1 NUMBER, t2_value_1 VARCHAR(10));
INSERT INTO t_result_set VALUES (100, 'Value2', 100, 'Value2');
INSERT INTO t_result_set VALUES (200, 'Value1', NULL, NULL);
INSERT INTO t_result_set VALUES (200, 'Value2', NULL, NULL);
INSERT INTO t_result_set VALUES (200, 'Value3', NULL, NULL);
-- basically I want to join table_2 to table_1 in manner that if there is a mach (inner join) between table_1 and table_2 then it is returned
SELECT *
FROM table_1 t_1
JOIN table_2 t_2 ON t_1.id_1 = t_2.id_1
AND t_1.value_1 = t_2.value_1;
--but if there is no mach then the left side of join is returned
SELECT *
FROM table_1 t_1
LEFT JOIN table_2 t_2 ON t_1.id_1 = t_2.id_1
AND t_1.value_1 = t_2.value_1
WHERE t_1.id_1 = 200;
--added where so it would skip t_1.id_1 = 100 where inner join is possible
-- pseudo logic it looks like this
/*
IF EXISTS (SELECT 1
FROM table_2 t_2
WHERE t_2.id_1 = t_1.id_1
AND t_2.value_1 = t_1.value_1) THEN
INNER JOIN
ELSE
LEFT JOIN
*/
--when the two previous statements are combined the desirable result should look like this
SELECT *
FROM t_result_set;
Thanks!
You mean something like this:
CREATE TABLE table_1 (id_1 NUMBER, value_1 VARCHAR2(10));
INSERT INTO table_1 VALUES (100, 'Value1');
INSERT INTO table_1 VALUES (100, 'Value2');
INSERT INTO table_1 VALUES (100, 'Value3');
INSERT INTO table_1 VALUES (200, 'Value1');
INSERT INTO table_1 VALUES (200, 'Value2');
INSERT INTO table_1 VALUES (200, 'Value3');
--table which should be joined
CREATE TABLE table_2 (id_1 NUMBER, value_1 VARCHAR2(10));
INSERT INTO table_2 VALUES (100, 'Value2');
select *
from table_1 t1
left join table_2 t2
on t1.id_1 = t2.id_1
where t1.value_1 = t2.value_1 or t2.id_1 is null
order by 1;
ID_1 VALUE_1 ID_1 VALUE_1
100 Value2 100 Value2
200 Value2
200 Value1
200 Value3
By placing "t1.value_1 = t2.value_1" in the where, this becomes an inner join. But if there's no matching id_1 value in t2 it remains an outer join.