Basic Query Questions 2

[daglugub37]

I am trying to learn how to design relational databases. I started a football statistice database in Access but decided it would be better to learn SQL server instead. I imported the few tables I populated in Access and now would like to figure out how to query.

Here is one table:
tblPlayers
fldPlayerID - int
fldFirst - nvarchar
fldLast - nvarchar
fldPosition - nvarchar

another table:
tblTeams
fldTeamID - int
fldName - nvarchar

last table:
tblRoster
fldRosterID - int
fldTeamID - int
fldPlayerID - int
fldSeason - int
fldStarter - bit(yes/no field)

2 main questions.
A: Did the relationships I establised in Access copy over or is it a whole different scheme in SQL.

B: How do I query for example the Name of all the Starters on the Dolphins in 2003???

 

[donutman]

Select fldFirst, fldLast, fldPosition from tblTeams T inner join tblRoster R on T.TeamID=R.TeamID inner join
tblPlayers P on P.fldPlayerID=R.fldPlayerID
where fldSeason=2003 and fldName=N'Dolphins' and fldStarter<>0
Are you asking if the import established foreign key constraints? Don't know, but you can check.
-Karl

 

[daglugub37]

thats cool it works!...Now after running that it in enterprise mgr views, it shows me the results and grapically how joins work.

Except why do I get 4 hits for each individual starter?

If you could figure that out I would be much appreciated but you have already shown me a great example of joins...which I will delve into further.

thanks.

 

[donutman]

Probably have multiple entries in Roster table for each player. Add the fldRosterID to the select list and see if there are 4 values for each.
-Karl

 

[daglugub37]

Nope, same fldRosterID. Anyway I am getting ahead of my understanding here so no worries on figuring that out.

I have read a little on inner joins and I understand the theory in examples with 2 tables, but I am not quite grasping how the above inner join is working with more than 2 tables. Anywho I will study up on joins.

One last question,

on a simple select like..
select * from tblPlayers where fldposition = 'QB'

How can I modify this so the position is a variable; and when run you can plug in any valid position like HB or WR to get those results?

 

[SQLBill]

Like this:

CREATE PROCEDURE myproc
  @position VARCHAR(5)
AS
SELECT *
FROM tblPlayers
WHERE fldPosition = @position

That creates a stored procedure. You then run the sp as:

EXEC myproc 'QB'

Refer to the BOL, use the Index tab and enter Stored Procedure, then choose the option that has Transact SQL in the Location column.

-SQLBill
BOL = Books OnLine = Microsoft SQL Server Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[donutman]

It would be a good idea to research why you are getting multiple records for that original query. Check all three tables for duplicates and decide if that is what you want.
-Karl

 

[daglugub37]

thanks again all.