Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Steve2106
Posting Yak Master
183 Posts |
 Posted - 2007-06-27 : 16:40:39
|
| Hi Guys,I have 2 tables.Table1 (Elements)SectionId | Description1 | Polices2 | RisksTable2 (Topics)TopicId | SectionId | Description | URL1 | 1 | Oil | Page1.htm2 | 1 | Water | Page2.htm3 | 1 | Petrol | Page3.htm4 | 1 | Fire | Page4.htm5 | 2 | oil | Page5.htm6 | 2 | Fire | Page6.htmI need a select statement to create a single dataview / datasourcewith this result.ID | ParentID | Text | URL1 | null | Polices | null (From Elements Table)2 | null | Risks | null (From Elements Table)3 | 1 | Oil | Page1.htm (From Topics Table)4 | 1 | Water | Page2.htm (From Topics Table)5 | 1 | Petrol | Page3.htm (From Topics Table)6 | 1 | Fire | Page4.htm (From Topics Table)7 | 2 | oil | Page5.htm (From Topics Table)8 | 2 | Fire | Page6.htm (From Topics Table)Any ideas?Thanks for your help I really appreciate it.Best Regards,Steve.Steve |
|
|
phdiwakar
Starting Member
15 Posts |
Posted - 2007-06-27 : 17:10:17
|
| [code]select null as ParentID, Description as [Text], null as URL from Table1UNION ALLselect SectionID as ParentID, Description as [Text], URL from Table2[/code]If you need the ID also as indicated in the results, you need to use a temporary table with Identity column and insert all these into that. |
 |
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2007-06-27 : 17:17:26
|
| Hi phdiwakar,Thanks for the reply.I will try your answer tomorrow, it looks good to me.I do need to have an ID. How would I create the temporary table and insert the results of the selects into it.Thanks again for your help, I appreciate it.Best regards,Steve.Steve |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-28 : 09:16:41
|
| Create a temp table with needed columns and doInsert into #temp(columns)Your union all queryMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Resuls from two Tables