[SOLVED] Joining 3 columns?

[tamumech]

Hi 

 

I am making a database of baseball players and their positions.  Each player usually has at least two positions.  I have two tables like this (simplified):

 

Players:

 

position1position2

31

23

12

 

 

Positions:

 

Position IDPosition

1First Base

2Second Base

3Third Base

 

I need to do a joins so that I can print both positions and not the position IDs, but I don't know how to do that when there are 3 columns?  I have had no luck so far.

 

Thanks!

-Tim

 

 

 

[tamumech]

Well I guess what I'll have to do is just make another position table (a duplicate of the first) and just do a regular LEFT JOIN for the three tables.  If anyone knows a better way to do it please post.

[Illusion]

Can u post the sample result u r expecting ?

[kirk112]

Think this will work,  not tested it though

 

SELECT p1.position AS position1, p2.position AS position2

FROM players AS py

LEFT JOIN positions AS p1 ON py.position1 = p1.position_id

LEFT JOIN positions AS p2 ON py.position2 = p2.position_id

[tamumech]

Can u post the sample result u r expecting ?

 

Sorry about that.  Here is a clearer version:

 

table name = players

 

player_idposition1position2

00352

00431

00523

 

table name = positions

 

position_idposition

11B

22B

33B

4SS

5OF

 

The result I want is:

 

player_idposition1position2

003OF2B

0043B1B

0052B3B

 

 

kirk112,

Can you explain what p1, p2, and py represent?  I have also tried to do two LEFT JOINs but have gotten an error message because you can't join the same table twice.  I haven't used the AS statement yet though...

 

 

 

[kirk112]

The p1, p2, py are alias for the tables, you will need to use them if you want to join the same table twice.

 

py is the reference for the players table.

 

so the sql would be...

 

SELECT py.player_id, p1.position AS position1, p2.position AS position2

FROM players AS py

LEFT JOIN positions AS p1 ON py.position1 = p1.position_id

LEFT JOIN positions AS p2 ON py.position2 = p2.position_id

[tamumech]

The p1, p2, py are alias for the tables, you will need to use them if you want to join the same table twice.

 

py is the reference for the players table.

 

so the sql would be...

 

SELECT py.player_id, p1.position AS position1, p2.position AS position2

FROM players AS py

LEFT JOIN positions AS p1 ON py.position1 = p1.position_id

LEFT JOIN positions AS p2 ON py.position2 = p2.position_id

 

That worked!

 

Thanks kirk!