Not sure how to attack this join problem

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

Not sure how to attack this join problem

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2010-07-09 : 22:48:12

There are two tables: Teams and Grades.

Teams

Team | O | A | D
Army | A | B | C
Aubu | F | D | A

Grades

G | Num
A | 5
B | 4
C | 3
D | 2
E | 1
F | 0

I wanted to display the full results of the Teams table but also the numerical value that match each team's grade.

Eg.

Team | O | A | D | 1 | 2 | 3
Army | A | B | C | 5 | 4 | 3
Aubu | F | D | A | 0 | 1 | 5

I tried this....

SELECT a.team, a.overall, a.offense, a.defense,

b.num Overall, b.num Offense, B.num defense

FROM teamrank a

left JOIN ncaagrades b

on a.overall = b.grade or a.offense=b.grade or a.defense=b.grade;

but it is obviously wrong, all the number values are the same

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-10 : 01:42:33

try this:



select 
O
,A
,D
,case when O =  'A' then 5
      when O =  'B' then 4
      when O =  'C' then 3
      when O =  'D' then 2
      when O =  'E' then 1
      when O =  'F' then 0 end '1' 
,case when A =  'A' then 5
      when A =  'B' then 4
      when A =  'C' then 3
      when A =  'D' then 2
      when A =  'E' then 1
      when A =  'F' then 0 end '2' 
,case when D =  'A' then 5
      when D =  'B' then 4
      when D =  'C' then 3
      when D =  'D' then 2
      when D =  'E' then 1
      when D =  'F' then 0 end '3' 
from team

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-10 : 03:34:37

[code]
declare @teams table(team char(4), O char(1), A char(1), D char(1))
insert @teams values
('Army' , 'A', 'B' , 'C'),
('Aubu' , 'F', 'D' ,'A')

declare @Grades table (G char(1), num int)
insert @Grades values
('A' , 5),
('B' , 4),
('C' , 3),
('D' , 2),
('E' , 1),
('F' , 0)

SELECT team,
MAX(CASE WHEN part = 'O' THEN value END) AS 'O',
MAX(CASE WHEN part = 'A' THEN value END) AS 'A',
MAX(CASE WHEN part = 'D' THEN value END) AS 'D',
MAX(CASE WHEN part = 'O' THEN num END) AS '1',
MAX(CASE WHEN part = 'A' THEN num END) AS '2',
MAX(CASE WHEN part = 'D' THEN num END) AS '3'
FROM
(
SELECT team, 'O' AS part, O AS value
FROM @teams
UNION ALL
SELECT team, 'A', A
FROM @teams
UNION ALL
SELECT team, 'D', D
FROM @teams
) D
JOIN @Grades G
ON D.value = G.G
GROUP BY team

/* Result
team O A D 1 2 3
---- ---- ---- ---- ----------- ----------- -----------
Army A B C 5 4 3
Aubu F D A 0 2 5
*/
[/code]

______________________

sqlchiq
Posting Yak Master

133 Posts

Posted - 2010-07-10 : 11:55:31

SELECT team,
MAX(CASE WHEN part = 'Overall' THEN value END) AS 'Overall',
MAX(CASE WHEN part = 'Offense' THEN value END) AS 'Offense',
MAX(CASE WHEN part = 'Defense' THEN value END) AS 'Defense',
MAX(CASE WHEN part = 'Overall' THEN num END) AS 'Overalln',
MAX(CASE WHEN part = 'Offense' THEN num END) AS 'Offensen',
MAX(CASE WHEN part = 'Defense' THEN num END) AS 'Defensen'
FROM
(
SELECT team, 'Offense' AS part, Offense AS value
FROM teamrank
UNION ALL
SELECT team, 'Offense', Offense
FROM teamrank
UNION ALL
SELECT team, 'Defense', Defense
FROM teamrank
) D
JOIN ncaagrades g
ON D.value = g.Grade
GROUP BY team

When I run the query i get blank entries. Is there something wrong with my code?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-10 : 12:07:51

Your original query was fine, you just needed to add some joins:

SELECT a.team, a.overall, a.offense, a.defense, x.Num [1], y.Num [2], z.Num [3]
FROM teamrank a
LEFT JOIN ncaagrades x ON a.overall=x.Grade
LEFT JOIN ncaagrades y ON a.offense=y.Grade
LEFT JOIN ncaagrades z ON a.defense=z.Grade

sqlchiq
Posting Yak Master

133 Posts

Posted - 2010-07-10 : 13:04:44

Thank you Rob, I knew there was an easier way to do it

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-10 : 14:00:33

quote:
Originally posted by sqlchiq

SELECT team,
MAX(CASE WHEN part = 'Overall' THEN value END) AS 'Overall',
MAX(CASE WHEN part = 'Offense' THEN value END) AS 'Offense',
MAX(CASE WHEN part = 'Defense' THEN value END) AS 'Defense',
MAX(CASE WHEN part = 'Overall' THEN num END) AS 'Overalln',
MAX(CASE WHEN part = 'Offense' THEN num END) AS 'Offensen',
MAX(CASE WHEN part = 'Defense' THEN num END) AS 'Defensen'
FROM
(
SELECT team, 'Offense' AS part, Offense AS value
FROM teamrank
UNION ALL
SELECT team, 'Overall', Overall
FROM teamrank
UNION ALL
SELECT team, 'Defense', Defense
FROM teamrank
) D
JOIN ncaagrades g
ON D.value = g.Grade
GROUP BY team

When I run the query i get blank entries. Is there something wrong with my code?

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-10 : 14:15:52

quote:
Originally posted by sqlchiq

Thank you Rob, I knew there was an easier way to do it

Best is to create table in open schema structure.
Your teamRank is denormalized table. So my recommend is first declaring and create a normalized table then you can declare referential integrity and cascading update and delete on foreign key.

With current schema you cannot add three foreign key on teamRank with ON DELETE and ON UPDATE CASCADE because SQL Server do not allow you have "multiple cascading paths".

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources