Duplicate rows when joining tables

Question

I am having troubles with a SQL. My problem is that I get alot of duplicate rows but I don't know how to fix it.

I have the following tables:

tblCGG with columns: listId, description

tblCLA with columns: listid, CLADescription

tblHEA with columns: listid, HEADescription

tblACT with columns: listid, ACTDescription

If I run these tables seperatly with listid = '132623' I get the following output:

tblCGG: 1 row

tblCLA: 4 rows

tblHEA: 10 rows

tblACT: 4 rows

I want to join these tables together, but I am getting way to many rows.

I tried this query below, but I get 160 rows:

select distinct cgg.listid, cla.claDescription, hea.heaDescription, 
act.actDescription
from tblCGG cgg
left join tblCLA cla on cgg.listid = cla.listid
left join tblHEA hea on cgg.listid = hea.listid
left join tblACT act on cgg.listid = act .listid
where cgg.listid = '132623'

Desired Output

listid    claDescription   heaDescription            actDescription
132623          claTest        hea1                      act1
132623          clads          hea2                      act2
132623          cloas          hea3                      act3
132623          ccaa           hea4                      act4
132623          null           hea5                      null
132623          null           hea6                      null
132623          null           hea7                      null
132623          null           hea8                      null
132623          null           hea9                      null
132623          null           hea10                     null

Answer 1

I am not sure if desired output really has sense. But if it is what you really, REALLY need then.

select coalesce(t.listid, c.listid, a.listid, h.listid) listid, 
       cladescription, headescription, actdescription
  from tblcgg t
  FULL OUTER join (select a.*, row_number() over(partition by listid order by cladescription) seq_no from tblcla a) c on t.listid=c.listid
  FULL OUTER join (select a.*, row_number() over(partition by listid order by actdescription) seq_no from tblact a) a on t.listid=a.listid and a.seq_no=c.seq_no
  FULL OUTER join (select a.*, row_number() over(partition by listid order by headescription) seq_no from tblhea a) h on h.listid=a.listid and (h.seq_no=c.seq_no or h.seq_no=a.seq_no) 
 where coalesce(t.listid, c.listid, a.listid, h.listid)=132623

I am a bit upset with this code as performance will be low on bigger datasets but can't quickly find better solutions without writing function. Few words of code explanation:

• row_number() is window function for obtaining sequence number of each description in each table (you can play with "order by" in it for desired ordering)
• full outer join is something that shouldn't be used lightly as performance is not a good side of it but you want a rather strange output so it is good for it
• coalesce() returns first not null value

You really should think if union all descriptions will not be better for you:

select listid, 'cgg' source,description from tblcgg where listid=132623
UNION ALL
select listid, 'act' source,actdescription from tblact where listid=132623
UNION ALL
select listid, 'head' source,headescription from tblhea where listid=132623
UNION ALL
select listid, 'cla' source,cladescription from tblcla where listid=132623

Answer 2

The following query will give the results you're looking for. It's a slight mod of your original, but depends on knowing that tblHEA has the most rows in it:

WITH ctecla as (select listid, cladescription, rownum as cla_rownum from tblcla),
     ctehea as (select listid, headescription, rownum as hea_rownum from tblhea),
     cteact as (select listid, actdescription, rownum as act_rownum from tblact)
select cgg.listid,
       cla.claDescription,
       hea.heaDescription,
       act.actDescription
  from tblCGG cgg
  left join cteHEA hea
    on hea.listid = cgg.listid
  left join cteCLA cla
    on cla.listid = hea.listid AND
       cla.cla_rownum = hea.hea_rownum
  left join cteACT act
    on act.listid = hea.listid AND
       act.act_rownum = hea.hea_rownum
  where cgg.listid = '132623';

SQLFiddle here

Answer 3

You want a separate list in each column. This isn't really a SQL'ish thing to do, but you can arrange it. One method uses row_number() and group by:

select listid, max(claDescription) as claDescription,
       max(heaDescription) as heaDescription,
       max(actDescription) as actDescription
from ((select cla.listid, cla.claDescription, NULL as heaDescription, NULL as actDescription,
              row_number() over (partition by cla.listid order by cla.listid) as seqnum
       from tblCLA cla
      ) union all
      (select hea.listid, NULL as claDescription, hea.heaDescription, NULL as actDescription,
              row_number() over (partition by hea.listid order by hea.listid) as seqnum
       from tblHEA hea
      ) union all
      (select act.listid, NULL as claDescription, NULL as heaDescription, act.actDescription,
              row_number() over (partition by act.listid order by act.listid) as seqnum
       from tblACT act
      )
     ) x
where listid = 132623  -- only use single quotes if this is really a string
group by listid, seqnum;