0

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.

5
  • I removed the extraneous Microsoft SQL Server and MySQL tags from your question. Please don't specify tags unrelated to your question. – Dan Guzman Jun 22 '20 at 9:46
  • edit the question - pick ONE sql only. Which database version do you want to use? Include the link to the fiddle. What is the expected the results for your query? When there is a duplicate what is the criteria for for the row you want selected? – danblack Jun 22 '20 at 9:46
  • @Danblack ... Thanks for your response... I have updated query.. – Kathir E Jun 22 '20 at 10:04
  • You have 1 row in test2 that matches 3 rows in test1. If all rows from test1 should be included in result, what is the expected output of your query? – Lennart Jun 22 '20 at 10:04
  • Also, you have both DISTINCT and GROUP BY in your query, that is redundant because in this context they do the same thing – Lennart Jun 22 '20 at 10:06
1

Doing it on client side (sqlplus):

SQL> 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;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> break on value
SQL> 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;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4

In SQL:

SQL> clear breaks
breaks cleared
SQL> 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;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, case when row_number() over (partition by t1.id order by t1.id) = 1 then t2.value end 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;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.