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 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 1 6 5
123456 2 4 5
Description:
Expected Result is , when i m fetching match records from tables. Should get all the records from left table(Test1) and avoid duplicate record from right table (Test2).
Expected Query Result are,
ID TYPE other value
123456 0 2 5
123456 1 6
123456 2 4
SQL Fiddle Link
http://sqlfiddle.com/#!9/8953fc/29
Criteria
When there is ID match should get all matched records from left table (Test1) and from right table (Test2) should get distinct records.
Best Answer
Doing it on client side (sqlplus):
In SQL: