hej613
asked on
Data Normalization Mult joins to one table
Good morning Experts.
I was looking at a simple'ish database today and came across a problem with trying to join one table to another multiple times. For this example, I was thinking of a Stat's program for a football team.
Table 1 is a "roster"
Roster_ID
Roster_number
roster_name
(this can be more complicated, but gives you an idea)
Table two would contain the "Plays" that the teams ran, lets say I want to list that one of the players passed the ball to another
Plays:
Play_ID
play_Down
play_PassBy (FK connected to roster table)
play_catchBy(FK Connected to roster table)
play_runby (FK Conneted to roster table)
I would not be able to join each of the passby/catchby/runby back to the same roster table, can I?
Some plays will have values in the catchby and runby, so most joins will either show one value or another - how would you write a query to get around this?
Thank you - I hope my example gives you the details you need to figure out what I'm talking about! :)
I was looking at a simple'ish database today and came across a problem with trying to join one table to another multiple times. For this example, I was thinking of a Stat's program for a football team.
Table 1 is a "roster"
Roster_ID
Roster_number
roster_name
(this can be more complicated, but gives you an idea)
Table two would contain the "Plays" that the teams ran, lets say I want to list that one of the players passed the ball to another
Plays:
Play_ID
play_Down
play_PassBy (FK connected to roster table)
play_catchBy(FK Connected to roster table)
play_runby (FK Conneted to roster table)
I would not be able to join each of the passby/catchby/runby back to the same roster table, can I?
Some plays will have values in the catchby and runby, so most joins will either show one value or another - how would you write a query to get around this?
Thank you - I hope my example gives you the details you need to figure out what I'm talking about! :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Think of it as:
3 views from roaster table.
roaster_PassBy
roaster_catchBy
roaster_runby
Join the views to the Plays table relating the respective fields.
The same effect can be achieved using aliasses to the table. (From table alias)
3 views from roaster table.
roaster_PassBy
roaster_catchBy
roaster_runby
Join the views to the Plays table relating the respective fields.
The same effect can be achieved using aliasses to the table. (From table alias)
ASKER
Obviously I need to go to Join school again - I'm not 100% sure on why these work... on #1 there is a case statement, and I'm not sure what the point is of returning 1/0....
number 2 I only see it "FROM" being one table, and then the second table named on the join...
number 2 I only see it "FROM" being one table, and then the second table named on the join...
It's quite simple: Although each join specification joins only two tables, a FROM clause can contain multiple join specifications. This allows many tables to be joined for a single query. Or in your case to use the same table to join multiple times to retrieve different data.
#1: Summing 1 and 0 allows you to account each Roster for his Pass/Catch/Run
can you give some sample data and expected output?
the above examples show how various joins "could" be done, but we're all just guessing as to what kind of results you're looking for.
the above examples show how various joins "could" be done, but we're all just guessing as to what kind of results you're looking for.
ASKER
Absolutely - Here is a quick example I threw together: (Thanks all for the different examples!)
CREATE TABLE STATS.ROSTER
(
R_ID NUMBER NOT NULL,
R_NUM NUMBER,
R_NAME VARCHAR2(50 BYTE),
R_POSITION VARCHAR2(20 BYTE)
)
CREATE TABLE STATS.OFF_PLAY
(
P_ID NUMBER NOT NULL,
P_GAME NUMBER,
P_DOWN VARCHAR2(15 BYTE) NOT NULL,
P_TYPE VARCHAR2(5 BYTE),
P_LOCATION VARCHAR2(15 BYTE),
P_RUNBY NUMBER,
P_THROWBY NUMBER,
P_CATCHBY NUMBER,
P_YARDS NUMBER,
P_SCORETYPE VARCHAR2(10 BYTE),
P_POINTS VARCHAR2(1 BYTE),
P_FUMBLE VARCHAR2(1 BYTE),
P_INTERCEPTION VARCHAR2(1 BYTE),
P_COMPLETION VARCHAR2(1 BYTE),
P_INCOMPLETE VARCHAR2(1 BYTE),
P_BALLLOC VARCHAR2(3 BYTE),
P_BALLYRD NUMBER
)
Example roster information:
r_id r_num r_name r_position
1 4 Caleb N
2 7 Mark R
3 10 Dillon W QB
4 14 Kyle K RB SS
5 20 Dustin J
6 21 Jordan Sellers RB WR
insert into off_play
VALUES (4, 1, 1,'P','L_OT_L','',3,9,12,'NA','','','','','','OWN','34';);
insert into off_play
VALUES (3, 1, 2,'P','L_OT_L','',5,6,12,'NA','','','','','','OWN','34';);
ASKER
If you notice on this example - on p_id#4 there is a value for catchby and throwby (both linking over to the roster table) in my experimentation I would get a value in one of these, but not the other.
>>> in my experimentation I would get a value in one of these, but not the other.
is that desired? or not?
given the data above, what do you "want" returned?
is that desired? or not?
given the data above, what do you "want" returned?
ASKER
I would want to see both names, I would want to know player in rosters r_id #3 (r_num 10, dillian w) threw to #9 and show details of both names in the query....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window