Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data Normalization Mult joins to one table

Posted on 2011-09-26
13
Medium Priority
?
312 Views
Last Modified: 2012-05-12
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! :)



0
Comment
Question by:hej613
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 36708458
Your Stat query can be like the below SQL:
SELECT r.Roster_number, r.roster_name, Sum(CASE WHEN r.Roster_ID = p.play_PassBy THEN 1 ELSE 0 END) NbPass , Sum(CASE WHEN r.Roster_ID = p.play_catchBy THEN 1 ELSE 0 END) NbCatch, Sum(CASE WHEN r.Roster_ID = p.play_runby THEN 1 ELSE 0 END) NbRun
FROM Plays p INNER JOIN Roster r ON r.Roster_ID in (p.play_PassBy, p.play_catchBy, p.play_runby)
GROUP BY r.Roster_number, r.roster_name

Open in new window

0
 
LVL 35

Accepted Solution

by:
ste5an earned 1600 total points
ID: 36708611
You can join a table multiple times:

SELECT P.* ,
            PB.* ,
            CB.* ,
            RB.*
FROM    Plays P
            LEFT JOIN Roster PB ON P.play_PassBy = PB.Roster_ID
            LEFT JOIN Roster CB ON P.play_CatchBy = CB.Roster_ID
            LEFT JOIN Roster RB ON P.play_RunBy = RB.Roster_ID ;

Open in new window

0
 
LVL 31

Expert Comment

by:hnasr
ID: 36708932
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)
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:hej613
ID: 36709216
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...

0
 
LVL 35

Expert Comment

by:ste5an
ID: 36709272
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.
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 36709290
#1: Summing 1 and 0 allows you to account each Roster for his Pass/Catch/Run
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36709953
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.  
0
 
LVL 1

Author Comment

by:hej613
ID: 36711041
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';);

Open in new window

0
 
LVL 1

Author Comment

by:hej613
ID: 36711055
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.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36711203
>>> 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?
0
 
LVL 1

Author Comment

by:hej613
ID: 36711329
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....
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 36711776
http:#36708611  seems to be what you want then...


here slightly adjusted for your actual column names

SELECT P.* ,
            PB.r_name throwname,
            CB.r_name catchname,
            RB.r_name runname
FROM    off_play P
            LEFT JOIN Roster PB ON P.p_throwBy = PB.R_ID
            LEFT JOIN Roster CB ON P.p_CatchBy = CB.R_ID
            LEFT JOIN Roster RB ON P.p_RunBy = RB.R_ID ;
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question