Prevent duplicate record when inner join query in SQL

user20061612 0
Viewed 20

I used the inner join command to get the data from two tables. But, when I run the SQL query.

I got the same record duplicated 48 times.

The SQL query I created is below

SELECT 
    ABS_LIMIT.B1_NAME, ABS_LIMIT.B2_NAME, ABS_LIMIT.B3_NAME, ABS_LIMIT.ELEM_NAME 
FROM 
    ABS_LIMIT
INNER JOIN 
    RTU_SCAN ON RTU+SCAN.B1_NAME = ABS_LIMIT.B1_NAME
WHERE 
    ABS_LIMIT.B3_NAME LIKE 'AMP%';

Does anyone have any idea how to remove the duplicate from the query result?

oracle inner-join sql
1 Answers

You never SELECT any columns from RTU_SCAN so you can use EXISTS rather than an INNER JOIN:

SELECT a.B1_NAME, 
       a.B2_NAME,
       a.B3_NAME,
       a.ELEM_NAME 
FROM   ABS_LIMIT a
WHERE  EXISTS (SELECT 1 FROM RTU_SCAN r WHERE r.B1_NAME = a.B1_NAME)
AND    a.B3_NAME LIKE 'AMP%';

Then, if there are duplicates in RTU_SCAN they will not propagate duplicate rows in the output.

Alternatively, you could use DISTINCT to remove duplicates:

SELECT DISTINCT
       a.B1_NAME, 
       a.B2_NAME,
       a.B3_NAME,
       a.ELEM_NAME 
FROM   ABS_LIMIT a
       INNER JOIN RTU_SCAN r
       ON r.B1_NAME = a.B1_NAME
AND    a.B3_NAME LIKE 'AMP%';

However, it will probably be less efficient to generate duplicates and then filter them out using DISTINCT compared to using EXISTS and not generating the duplicates in the first place.

MT00
Related