Need help with a one-to-many table join

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

Database Design and Application Architecture

Need help with a one-to-many table join

Author

Topic

daerne
Starting Member

3 Posts

Posted - 2011-08-09 : 11:49:29

I'm not entirely sure this database was built correctly, however I need to pull data out of it to build student attendance cards.

I'm trying to join a Student table with a Rank table. The rank table holds all of the ranks obtained and the dates they received them. When I attempt to display the data, I have to cycle through the same persons name equaling the amount of ranks they hold...then it will move onto the next person. I'm guessing that I have to modify my SELECT statement further, however I've reached the extent of my knowledge in that area.

Here is what I'm using:

SELECT tStudents.[Last Name], tStudents.[First Name], tStudents.email, tStudents.Street, tStudents.[Current Rank], tRank.Craft, tRank.[Craft - Rank - Category], tRank.[Date Assigned], tRank.[Date Completed]

FROM

(tStudents INNER JOIN tRank ON tStudents.[Index] = tRank.[Student Index])

I would like it to display the student name one time, with the ability to show all of the ranks on the same line.

Thanks...

David Aerne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 12:15:47

do you mean ranks all shown as comma separated list against each student name or you want different ranks to appear in different columns against a studentname?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daerne
Starting Member

3 Posts

Posted - 2011-08-09 : 12:43:44

Show them as separate columns against the same student name.

Something like:

<student> <personal info> <rank#1> <rank#1 date assigned> <rank#2> <rank#2 date assigned>

David Aerne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 12:44:58

how many ranks can come maximum for a student?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daerne
Starting Member

3 Posts

Posted - 2011-08-09 : 13:00:41

20 to reach the maximum rank.

David Aerne

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-09 : 13:05:54

EDIT: Ahhhh... now I see. You didn't mean Xml... you were using those as placeholders. You do know that this is a bad design right?

Somehow, I don't think this is what you were looking for, but it is similar to what you asked for...

Declare @Student table (
StudentId int identity(1,1),
Name varchar(50),
Primary Key (StudentId)
)

Declare @StudentRank table (
StudentRankId int identity(1,1),
StudentId int,
RankId int,
RankDate datetime,
Primary Key (StudentRankId)
)

Declare @Rank table (
RankId int identity(1,1),
Description varchar(20)
)

Insert Into @Rank Select 'Genius'
Insert Into @Rank Select 'Intelligent'
Insert Into @Rank Select 'Competent'
Insert Into @Rank Select 'Satisfactory'
Insert Into @Rank Select 'Plain Dumb'
Insert Into @Rank Select 'Drooling'

Insert Into @Student Select 'John'
Insert Into @Student Select 'Sally'
Insert Into @Student Select 'Mark'
Insert Into @Student Select 'Corey'

Insert Into @StudentRank Select 1, 2, '8/1/2011'
Insert Into @StudentRank Select 1, 3, '4/1/2011'
Insert Into @StudentRank Select 2, 1, '8/1/2011'
Insert Into @StudentRank Select 2, 2, '7/1/2011'
Insert Into @StudentRank Select 2, 3, '6/1/2011'
Insert Into @StudentRank Select 2, 4, '5/1/2011'
Insert Into @StudentRank Select 2, 5, '4/1/2011'
Insert Into @StudentRank Select 2, 6, '3/1/2011'
Insert Into @StudentRank Select 4, 6, '4/23/2011'

Select
StudentId,
Name,
Ranks = (
Select
[@RankType] = C.Description,
[@RankDate] = convert(varchar,B.RankDate,101)
From @StudentRank B
Inner Join @Rank C
On B.RankId = C.RankId
Where B.StudentId = A.StudentId
For Xml Path('Rank'), Type
)
From @Student A
For Xml Path('Student')

Corey

I Has Returned!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 13:37:46

[code]
SELECT tStudents.[Last Name], tStudents.[First Name], tStudents.email, tStudents.Street, tStudents.[Current Rank],
MAX(CASE WHEN rn=1 THEN tRank.Craft ELSE NULL END) AS Rank1,
MAX(CASE WHEN rn=1 THEN tRank.[Date Assigned] ELSE NULL END) AS RankDateAssigned1,
....
MAX(CASE WHEN rn=20 THEN tRank.Craft ELSE NULL END) AS Rank20,
MAX(CASE WHEN rn=20 THEN tRank.[Date Assigned] ELSE NULL END) AS RankDateAssigned20
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tStudents.[Last Name], tStudents.[First Name] ORDER BY tRank.Craft) AS rn,tStudents.[Last Name], tStudents.[First Name], tStudents.email, tStudents.Street, tStudents.[Current Rank], tRank.Craft, tRank.[Craft - Rank - Category], tRank.[Date Assigned], tRank.[Date Completed]

FROM

(tStudents INNER JOIN tRank ON tStudents.[Index] = tRank.[Student Index])
)t
GROUP BY tStudents.[Last Name], tStudents.[First Name], tStudents.email, tStudents.Street, tStudents.[Current Rank]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources