RSS

Re: Selecting one record to many records

Please support our MS SQL advertiser: Programming Forums

Selecting one record to many records

•

Join Date: Jul 2005

Location: Dallas, TX

Posts: 482

Reputation: campkev is an unknown quantity at this point

Rep Power: 4

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: Selecting one record to many records

Apr 24th, 2006

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:

Help with Code Tags

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

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

DaniWeb Home > Forums > Web Development > Databases > MS SQL > Selecting one record to...

Re: Selecting one record to many records

Selecting one record to many records

Re: Selecting one record to many records