Member Avatar for PoisonElves

Hi

I have 3 tables that I need to display in a report

TableA
idxA valueA
1, a
2, b
3, c

TableB
idxB bidxA valueB
1, 1, aa
2, 1, bb
3, 1, cc
4, 2, aa
5, 2, cc

TableC
idxC bidcA valueC
1, 1, dd
2, 1, ee
3, 2, ff
4, 2, gg
5, 2, hh
6, 2, ii

The resut I would like to display is
1 aa dd
1 bb ee
1 cc __
2 aa ff
2 cc gg
2 __ hh
2 __ ii

Does anyone have and idea and preferably an example on how to do something like this in SQL

Thanks

  • 2 Contributors
  • 1 Reply
  • 135 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by campkev
Member Avatar for campkev

not possible (or at least extremely difficult to do and to maintain) the way you have the database setup as there is no way (other than the order that they are entered in the database, which is rather unreliable) to tell how tables b and c are linked. However if you change your setup and have tableC look like this

idxC , bidcA ,bidcb ,valueC

then you can run your query like this:

select idxA, valueb, valuec from tablea, tableb, tablec
where (idxa = bidxa and idxa = bidca and bidcb=idxb) 
union
select idxA, null as valueb, valuec from tablea, tablec
where idxa = bidca and bidcb is null
union 
select idxA, valueb,null as  valuec from tablea, tableb
where idxa = bidxa and idxb not in (select bidcb from tablec where bidcb is not null)

order by  idxA asc,valueb asc, valuec asc

which will give you a result that looks like

idxA	valueb	valuec
1	aa	dd
1	bb	ee
1	cc	
2		hh
2		ii
2	aa	ff
2	cc	gg

slightly different order, but that is unavoidable as nulls come first when sorting alphabetically

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.