•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 430,769 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,570 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 1798 | Replies: 1
 |
•
•
Join Date: Apr 2006
Location: South Africa
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Selecting one record to many records 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
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
•
•
Join Date: Jul 2005
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
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:
which will give you a result that looks like
slightly different order, but that is unavoidable as nulls come first when sorting alphabetically
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
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 MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Duplicate record and child records (MS Access and FileMaker Pro)
- please anybody to help Dropdownlist selected index problem (ASP.NET)
- move previouse record in VB.net (VB.NET)
- assignment help [fstream, on modifying a record in a file] (C++)
- Help with a reservation program! GUI Messed XD (Java)
- Trouble Reading Records (C++)
- need to update each records after record deleted (PHP)
- Deleting a record from a file (C)
- Code not working for deleting recordfrom a file (Visual Basic 4 / 5 / 6)
Other Threads in the MS SQL Forum
- Previous Thread: SQL Golf Handicap
- Next Thread: SQLServer 2000 Scheduled Job recurring every second
