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 |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-06-09 : 16:31:09
|
| I'll show my schema first, then I'll explain what I'm doing:--------------------------------------------------Events--------------------------------------------------ID | E_Title----------------------------------------------------------------------------------------------------EventOptionGroups--------------------------------------------------ID | EOG_EventID | EOG_OptionGroupID----------------------------------------------------------------------------------------------------OptionGroups--------------------------------------------------ID | OG_Title----------------------------------------------------------------------------------------------------Options--------------------------------------------------ID | O_OptionGroupID | O_Description----------------------------------------------------------------------------------------------------EventRegistration--------------------------------------------------ID | ER_EventID | ER_Name----------------------------------------------------------------------------------------------------RegistrantOptions--------------------------------------------------ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection--------------------------------------------------There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:----------------------------------------------------------------------E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3----------------------------------------------------------------------Event | Bob | O_Description1 | O_Description2 | O_Description3So in that example, that event had 3 sessions.Right now, I can only output E_Title and ER_Name, I don't know how to output the session information |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 16:44:04
|
| It is very hard to help you as your column names are very confusing. It is a very bad practice to name a column ID. Identity columns should be named TableNameId or similar so that its meaning is the same in every table. Columns should not have different names in other tables when they have the same meaning. For instance, you should not name a column RO_EventRegistrationID in one table and then ID in the parent table. In this example, it should be named EventRegistrationID in both tables.You should also not prefix your columns like you are doing such as RO_. Could you rework your post so that it is much easier for us to follow?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-09 : 23:57:05
|
| You need to provide some sample data from your tables and also show us what you need as output from them. |
|
|
|
|
|
|
|
|
Output several columns based on rows