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 |
|
pueblo
Starting Member
3 Posts |
 Posted - 2014-08-27 : 17:16:12
|
Hi I have 3 tables that are relatively smallTableDrivercolumn 1 driver ID primary keycolumn 2 driver name varchar 50TableAutocolumn 1 auto ID primary keycolumn 2 Auto name varchar 50Table Racecolumn 1 race ID primary keycolumn 2 driver ID column 3 auto1 IDcolumn 4 auto2 IDI think in the race table I need to set the driver ID as foriegn key to driver table and auto1 ID and auto2 ID both as foriegn keys to auto table. Anyhow I am trying to create a query that gives merace ID, driver name, auto 1 name and auto 2 name and want to return all rows. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-27 : 17:31:28
|
| Yes setup FKs linking back to the parent table. This is for referential integrity purposes.But I think that the race table needs to be changed so that you only have one autoid column. You would just add 2 rows if you have 2 autos for that raceid/driverid combo.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
pueblo
Starting Member
3 Posts |
Posted - 2014-08-27 : 18:04:08
|
| Ok thanks for the quick reply. For the second row of the race table I think I would just copy the first row contents for the other columns with exception of the primary key. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-27 : 20:21:54
|
| Yes raceid, driverid would be the same, you'd just have different autoid values.Here's the query you originally asked for:select r.raceid, d.drivername, a.autonamefrom race rjoin driver d on r.driverid = d.driveridjoin auto a on r.autoid = a.autoidTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
pueblo
Starting Member
3 Posts |
Posted - 2014-08-28 : 13:51:45
|
| Ok thanks for the additional information. I noticed you used aliases for the table names, guess it makes the query easier to read. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-28 : 15:36:00
|
| I used the aliases to shorten the typing time. I do love aliases and always use them in my code where there are joins, but I did recently hate them. I was debugging a very long and complex stored procedure that was failing. One of the queries had over 10 joins. Each was using an alias, and the aliases chosen did not give me a clue as to which table it was, so I had to keep scrolling up and down to figure things out. It was very annoying. I can definitely see why people are against them when it comes to a proc like that.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
simple query