Selecting one record to many records

[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

[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