Search code examples
mysqlsqljoinplsqldistinct

Select non-dupilcate values from 5 tables which contains duplicate values in each table

A report contains data from 5 tables data. But every table contains duplicate values and returning each value 3-5 times. I can use distinct ID at start but report doesn't contain ID column. How can I select non duplicate values from all tables?

Select t1.name, t3.address, t4.phone,
case when t2.works is not null then 'Y' else 'N' end as employee,
case when t5.resident is not null then 'Y' else 'N' end as Resident

from table1 t1
inner join table2 t2 on t2.ID=t1.ID
inner join table3 t3 on t3.ID=t2.ID
....
inner join table5 t5 on t5.ID=t2.ID;
Solution

  • one way you can solve the problem is using CTE's

    with t1 as (
        select distinct <colnames...>
        from table1 
), t2 as (
        select distinct <colnames...>
        from table1
), .
   .
   .
select <columns>
from t1
inner join t2 on t2.ID = t1.ID
.
.
.