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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 simple query

Author  Topic 

pueblo
Starting Member

3 Posts

Posted - 2014-08-27 : 17:16:12
Hi I have 3 tables that are relatively small
TableDriver
column 1 driver ID primary key
column 2 driver name varchar 50

TableAuto
column 1 auto ID primary key
column 2 Auto name varchar 50

Table Race
column 1 race ID primary key
column 2 driver ID
column 3 auto1 ID
column 4 auto2 ID

I 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 me
race 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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.autoname
from race r
join driver d on r.driverid = d.driverid
join auto a on r.autoid = a.autoid

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -