SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 simple query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pueblo
Starting Member

3 Posts

Posted - 08/27/2014 :  17:16:12  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 08/27/2014 :  17:31:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/27/2014 :  18:04:08  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 08/27/2014 :  20:21:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/28/2014 :  13:51:45  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 08/28/2014 :  15:36:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000