SQL Query Help Needed

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Query Help Needed

stevepiercy
I need help building an SQL query.

Here are two simplified tables, with their columns:

games
--------
game_id
game_referee
game_ar1
game_ar2

referees
---------
referee_id
full_name


The games table has records like so:

 game_id     game_referee     game_ar1     game_ar2    
 ----------  ---------------  -----------  -----------
 90          507              443          60          
 91          323              494          33          
 92          356              506          299        


The referees table has records like so:

 referee_id    full_name        
 ------------  ----------------
 2             Andres Honegger        
 3             Eric Larson          
 4             Roy Gorman          


I want to generate results where the full_name of the referee is substituted into the appropriate slot, either game referee, game_ar1 or game_ar2, like so:

 game_id     game_referee     game_ar1         game_ar2    
 ----------  ---------------  -----------      -----------
 90          Andres Honegger  Eric Larson      Roy Gorman              
 91          Eric Larson      Roy Gorman       Andres Honegger              
 92          Roy Gorman       Andres Honegger  Eric Larson              


Any help would be greatly appreciated.

--steve
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Help Needed

Brett Harvey
basically,
select game_id, ref_name_no.full_name as ar1_name_no.full_name as
ar1_name, ar2_name_no.full_name as ar2_name
FROM games
Left Join referees ref_name_no ON games.game_referee=ref_name_no.referee_id
Left Join referees ar1_name_no ON games.game_referee=ar1_name_no.referee_id
Left Join referees ar2_name_no ON games.game_referee=ar2_name_no.referee_id

I think that covers it.

someone may have a more elegant solution, but this worked with what I
had to do.


>I need help building an SQL query.
>
>Here are two simplified tables, with their columns:
>
>games
>--------
>game_id
>game_referee
>game_ar1
>game_ar2
>
>referees
>---------
>referee_id
>full_name
>
>
>The games table has records like so:
>
>  game_id     game_referee     game_ar1     game_ar2  
>  ----------  ---------------  -----------  -----------
>  90          507              443          60        
>  91          323              494          33        
>  92          356              506          299        
>
>
>The referees table has records like so:
>
>  referee_id    full_name      
>  ------------  ----------------
>  2             Andres Honegger      
>  3             Eric Larson        
>  4             Roy Gorman        
>
>
>I want to generate results where the full_name of the referee is
>substituted into the appropriate slot, either game referee, game_ar1
>or game_ar2, like so:
>
>  game_id     game_referee     game_ar1         game_ar2  
>  ----------  ---------------  -----------      -----------
>  90          Andres Honegger  Eric Larson      Roy Gorman              
>  91          Eric Larson      Roy Gorman       Andres Honegger              
>  92          Roy Gorman       Andres Honegger  Eric Larson              
>
>
>Any help would be greatly appreciated.
>
>--steve
>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>Steve Piercy               Web Site Builder               Soquel, CA
><[hidden email]>                   <http://www.StevePiercy.com>
>
>--
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Help Needed

stevepiercy
In reply to this post by stevepiercy
I don't think this follows my table structure.  I have only one table for referees, ar1's and ar2's, not a separate table for each position.

--steve


On Monday, May 23, 2005, [hidden email] (Brett Harvey) pronounced:

>basically,
>select game_id, ref_name_no.full_name as ar1_name_no.full_name as
>ar1_name, ar2_name_no.full_name as ar2_name
>FROM games
>Left Join referees ref_name_no ON games.game_referee=ref_name_no.referee_id
>Left Join referees ar1_name_no ON games.game_referee=ar1_name_no.referee_id
>Left Join referees ar2_name_no ON games.game_referee=ar2_name_no.referee_id
>
>I think that covers it.
>
>someone may have a more elegant solution, but this worked with what I
>had to do.
>
>
>>I need help building an SQL query.
>>
>>Here are two simplified tables, with their columns:
>>
>>games
>>--------
>>game_id
>>game_referee
>>game_ar1
>>game_ar2
>>
>>referees
>>---------
>>referee_id
>>full_name
>>
>>
>>The games table has records like so:
>>
>>  game_id     game_referee     game_ar1     game_ar2  
>>  ----------  ---------------  -----------  -----------
>>  90          507              443          60        
>>  91          323              494          33        
>>  92          356              506          299        
>>
>>
>>The referees table has records like so:
>>
>>  referee_id    full_name      
>>  ------------  ----------------
>>  2             Andres Honegger      
>>  3             Eric Larson        
>>  4             Roy Gorman        
>>
>>
>>I want to generate results where the full_name of the referee is
>>substituted into the appropriate slot, either game referee, game_ar1
>>or game_ar2, like so:
>>
>>  game_id     game_referee     game_ar1         game_ar2  
>>  ----------  ---------------  -----------      -----------
>>  90          Andres Honegger  Eric Larson      Roy Gorman              
>>  91          Eric Larson      Roy Gorman       Andres Honegger              
>>  92          Roy Gorman       Andres Honegger  Eric Larson              
>>
>>
>>Any help would be greatly appreciated.
>>
>>--steve
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder               Soquel, CA
>><[hidden email]>                   <http://www.StevePiercy.com>
>>
>>--
>>------------------------------
>>Lasso Support: http://support.omnipilot.com/
>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>Manage your list subscription:
>>http://www.listsearch.com/lassotalk.lasso?manage
>
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Help Needed

Brett Harvey
In reply to this post by stevepiercy
Steve,

It does.  Notice the Left Join.  In essence, referees is being joined
3 times given the aliases ref_name_no, ar1_name_no and ar2_name_no.
It's done that way so that you can reference it 3 times, once for
each time needed (for the ref, ar1 and ar2)

Brett

>I don't think this follows my table structure.  I have only one
>table for referees, ar1's and ar2's, not a separate table for each
>position.
>
>--steve
>
>
>On Monday, May 23, 2005, [hidden email] (Brett Harvey) pronounced:
>
>>basically,
>>select game_id, ref_name_no.full_name as ar1_name_no.full_name as
>>ar1_name, ar2_name_no.full_name as ar2_name
>>FROM games
>>Left Join referees ref_name_no ON games.game_referee=ref_name_no.referee_id
>>Left Join referees ar1_name_no ON games.game_referee=ar1_name_no.referee_id
>>Left Join referees ar2_name_no ON games.game_referee=ar2_name_no.referee_id
>>
>>I think that covers it.
>>
>>someone may have a more elegant solution, but this worked with what I
>>had to do.
>>
>>
>>>I need help building an SQL query.
>>>
>>>Here are two simplified tables, with their columns:
>>>
>>>games
>>>--------
>>>game_id
>>>game_referee
>>>game_ar1
>>>game_ar2
>>>
>>>referees
>>>---------
>>>referee_id
>>>full_name
>>>
>>>
>>>The games table has records like so:
>>>
>>>   game_id     game_referee     game_ar1     game_ar2  
>>>   ----------  ---------------  -----------  -----------
>>>   90          507              443          60        
>>>   91          323              494          33        
>>>   92          356              506          299      
>>>
>>>
>>>The referees table has records like so:
>>>
>>>   referee_id    full_name      
>>>   ------------  ----------------
>>>   2             Andres Honegger      
>>>   3             Eric Larson        
>>>   4             Roy Gorman        
>>>
>>>
>>>I want to generate results where the full_name of the referee is
>>>substituted into the appropriate slot, either game referee, game_ar1
>>>or game_ar2, like so:
>>>
>>>   game_id     game_referee     game_ar1         game_ar2  
>>>   ----------  ---------------  -----------      -----------
>>>   90          Andres Honegger  Eric Larson      Roy Gorman            
>>>   91          Eric Larson      Roy Gorman       Andres Honegger            
>>>   92          Roy Gorman       Andres Honegger  Eric Larson            
>>>
>>>
>>>Any help would be greatly appreciated.
>>>
>>>--steve
>>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>>Steve Piercy               Web Site Builder               Soquel, CA
>>><[hidden email]>                   <http://www.StevePiercy.com>
>>>
>>>--
>>>------------------------------
>>>Lasso Support: http://support.omnipilot.com/
>>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>>Manage your list subscription:
>>>http://www.listsearch.com/lassotalk.lasso?manage
>>
>>
>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>Steve Piercy               Web Site Builder               Soquel, CA
><[hidden email]>                   <http://www.StevePiercy.com>
>
>--
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query Help Needed

stevepiercy
In reply to this post by stevepiercy
Aha!  Okay, there were a couple of naming errors in there, but this is how I got it to work:

select game_id,
  ref_name_no.RefLName as ref_name,
  ar1_name_no.RefLName as ar1_name,
  ar2_name_no.RefLName as ar2_name
FROM games
Left Join referees ref_name_no ON games.game_referee=ref_name_no.referee_id
Left Join referees ar1_name_no ON games.game_ar1=ar1_name_no.referee_id
Left Join referees ar2_name_no ON games.game_ar2=ar2_name_no.referee_id

Thank you!

--steve


On Tuesday, May 24, 2005, [hidden email] (Brett Harvey) pronounced:

>Steve,
>
>It does.  Notice the Left Join.  In essence, referees is being joined
>3 times given the aliases ref_name_no, ar1_name_no and ar2_name_no.
>It's done that way so that you can reference it 3 times, once for
>each time needed (for the ref, ar1 and ar2)
>
>Brett
>
>>I don't think this follows my table structure.  I have only one
>>table for referees, ar1's and ar2's, not a separate table for each
>>position.
>>
>>--steve
>>
>>
>>On Monday, May 23, 2005, [hidden email] (Brett Harvey) pronounced:
>>
>>>basically,
>>>select game_id, ref_name_no.full_name as ar1_name_no.full_name as
>>>ar1_name, ar2_name_no.full_name as ar2_name
>>>FROM games
>>>Left Join referees ref_name_no ON games.game_referee=ref_name_no.referee_id
>>>Left Join referees ar1_name_no ON games.game_referee=ar1_name_no.referee_id
>>>Left Join referees ar2_name_no ON games.game_referee=ar2_name_no.referee_id
>>>
>>>I think that covers it.
>>>
>>>someone may have a more elegant solution, but this worked with what I
>>>had to do.
>>>
>>>
>>>>I need help building an SQL query.
>>>>
>>>>Here are two simplified tables, with their columns:
>>>>
>>>>games
>>>>--------
>>>>game_id
>>>>game_referee
>>>>game_ar1
>>>>game_ar2
>>>>
>>>>referees
>>>>---------
>>>>referee_id
>>>>full_name
>>>>
>>>>
>>>>The games table has records like so:
>>>>
>>>>   game_id     game_referee     game_ar1     game_ar2  
>>>>   ----------  ---------------  -----------  -----------
>>>>   90          507              443          60        
>>>>   91          323              494          33        
>>>>   92          356              506          299      
>>>>
>>>>
>>>>The referees table has records like so:
>>>>
>>>>   referee_id    full_name      
>>>>   ------------  ----------------
>>>>   2             Andres Honegger      
>>>>   3             Eric Larson        
>>>>   4             Roy Gorman        
>>>>
>>>>
>>>>I want to generate results where the full_name of the referee is
>>>>substituted into the appropriate slot, either game referee, game_ar1
>>>>or game_ar2, like so:
>>>>
>>>>   game_id     game_referee     game_ar1         game_ar2  
>>>>   ----------  ---------------  -----------      -----------
>>>>   90          Andres Honegger  Eric Larson      Roy Gorman            
>>>>   91          Eric Larson      Roy Gorman       Andres Honegger            
>>>>   92          Roy Gorman       Andres Honegger  Eric Larson            
>>>>
>>>>
>>>>Any help would be greatly appreciated.
>>>>
>>>>--steve
>>>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>>>Steve Piercy               Web Site Builder               Soquel, CA
>>>><[hidden email]>                   <http://www.StevePiercy.com>
>>>>
>>>>--
>>>>------------------------------
>>>>Lasso Support: http://support.omnipilot.com/
>>>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>>>Manage your list subscription:
>>>>http://www.listsearch.com/lassotalk.lasso?manage
>>>
>>>
>>-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
>>Steve Piercy               Web Site Builder               Soquel, CA
>><[hidden email]>                   <http://www.StevePiercy.com>
>>
>>--
>>------------------------------
>>Lasso Support: http://support.omnipilot.com/
>>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>>Manage your list subscription:
>>http://www.listsearch.com/lassotalk.lasso?manage
>
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage