Tables Details:
CREATE TABLE Test2 (
ID INT,
Value INT,
other INT);
CREATE TABLE Test1 (
ID INT,
TYPE INT,
other INT);
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test2 VALUES (123456, 10, 17);
INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);
INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);
INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);
This is the Query i m using to avoid duplicate from table Test
SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t1.TYPE, t1.other, t2.value
ORDER BY t1.ID ASC;
Query Result:
ID TYPE other value
123456 0 2 5
123456 0 2 10
123456 1 6 5
123456 1 6 10
123456 2 4 5
123456 2 4 10
Description:
Expected Result is , when i m fetching match records from tables. Should get the records from left table(Test1) without duplicate records and avoid duplicate record from right table (Test2).
Reference Check Pic
Expected Query Result are,
23456 0 2 5
123456 1 6 10
123456 2 4
SQL Fiddle Link
http://sqlfiddle.com/#!9/3cd8a0/26
Criteria When there is ID match, should get matched records from left table (Test1) without duplicate and from right table (Test2) without duplicate.
Best Answer
Although you can JOIN the 2 tables ON their IDs, you'll probably need to introduce "artificial" rownumbers (as it were), and use these in your join, too.
TABLES and data
SELECT and ROW_NUMBER()
If you now JOIN these 2 result sets, you may be a step closer to the solution that you are after.
Now you just need to eliminate the rows whose IDs are not in Table2.
DBfiddle (Oracle 18c) SQLfiddle (Oracle 11g)