Selecting one record to many records

Expand Post »

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

Similar Threads

Deleting a record from a file in C
Duplicate record and child records in MS Access and FileMaker Pro
please anybody to help Dropdownlist selected index problem in ASP.NET
move previouse record in VB.net in VB.NET
assignment help [fstream, on modifying a record in a file] in C++

PoisonElves

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Apr 2006

Permalink

Apr 24th, 2006

Re: Selecting one record to many records

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:

MS SQL Syntax (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
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

MS SQL Syntax (Toggle Plain Text)
idxA	valueb	valuec
aa	dd
bb	ee
cc	
hh
ii
aa	ff
cc	gg
idxA	valueb	valuec
aa	dd
bb	ee
cc	
hh
ii
aa	ff
cc	gg

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL Golf Handicap

Next Thread in MS SQL Forum Timeline: SQLServer 2000 Scheduled Job recurring every second

DaniWeb Message

Cancel Changes

User Name
Password