CODEWITHSUNDEEP
sqloracle-database
KI1
KI1

Reputation: 929

Duplicate records using inner join with Three tables

I am currently working with 3 tables and I am finding difficulty in constructing a SQL query which returns unique set of records. I am using Oracle SQL.

If I include the UDG_Description fields I get duplicate records.

I have tried using 'distinct' on the Table1.MachId but I think that is incorrect way of using it for my query.

I noticed that if I include the UDG_Description I get duplicates but I need the description of the record from Table 3 in regards to the output i am trying to generate.

I appreciate if someone can assist me please?

Below is the current setup i am currently working with

Table1

Table2

Table3

Note The UDG_CODE is not unique. The udg_prefix and udg_id have to be unique.

SELECT distinct(MachId), MachCode, MachLinkType, Lev_UDG_Code, UDG_Description 
from Table1 INNER JOIN Table2
ON Table1.MachCode = Table2.Lev_MachCode
INNER JOIN Table2 ON
Table3.UDG_Code = Table2.Lev_UDG_Code
WHERE MachLinkType = 'ATX' AND Lev_UDG_Code = '12A89'

Kind Regards,

Upvotes: 1

Views: 4461

Answers (2)

BICube
BICube

Reputation: 4681

You need to get a unique list of codes/description from table3 before the join. Like such

SELECT MachId,
       MachCode,
       MachLinkType,
       Lev_UDG_Code,
       tbl3.UDG_Description 
FROM Table1
INNER JOIN Table2 ON Table1.MachCode = Table2.Lev_MachCode
INNER JOIN (SELECT UDG_CODE, 
                   UDG_Description 
            FROM Table3
            GROUP BY UDG_COD, 
                     UDG_Description) tbl3
ON tbl3.UDG_Code = Table2.Lev_UDG_Code

Upvotes: 1

sergdenisov
sergdenisov

Reputation: 8572

Maybe:

select distinct MachId,
                MachCode,
                MachLinkType,
                Lev_UDG_Code,
                UDG_Description 
from Table1
inner join Table2 on Table1.MachCode = Table2.Lev_MachCode
inner join Table3 on Table3.UDG_Code = Table2.Lev_UDG_Code
where MachLinkType = 'ATX' and Lev_UDG_Code = '12A89'

Upvotes: 0

Related Questions