[SOLVED] Matching tables

[bkpaul]

Sorry I think the subject title is a bit confusing  ???

 

Anyways Im a newbie using MYSql V5

 

I have 2 databases

 

In DB1 I have 2 columns

 

Recordnum          playername

(Primary Key)

      1                  Paul

      2                  david

      3                  michael

      4                  susan

      5                  liam

      6                  rachel

 

In DB2 I have 3 columns (amongst many) which contain primary key & fixture info (intiger)

 

home player & away Player

 

e.g.

 

Recordnum    hometeam  awaytem

(primary key)

 

    1                  1              2

    2                  3              4

    3                  5              6

 

I want to output the fixtures so instead of it displaying 1vs2, 3vs4, 4vs5 I want it to display paul vs david, michael vs susan, liam vs rachel etc

 

I know there is a really really simple answer to this but Im really really thick 

 

Any help would be appreciated.

 

 

[fenway]

You just need to join in the players table.

[bkpaul]

Thanks for the reply but how would I do that in a SQL statement, as said I am a newbie at this (albeit a newbie that wants to learn)

 

cheers

[bkpaul]

anyone? before I run out of hair to pull out 

[fenway]

Post what you have so far, and I'll show you how to inject this.

[bkpaul]

Thanks for the offer Fenway its very appreciated but I received help from another site

 

However in everyones interest I shall complete this topic with the answer I was given so as to help other newbies like me

 

the answer given was:-

 

SELECT t1.teamname, t2.teamname FROM results

INNER JOIN teams t1 ON hometeam = t1.teamsindex

INNER JOIN teams t2 ON awayteam = t2.teamsindex;

 

And the explination I was given which I thought was very useful was:-

 

SELECT * FROM results

INNER JOIN teams ON hometeam = teamsindex;This simply takes all rows in results and matches them up with the corresponding row in teams, corresponding meaning that hometeam = teamsindex.

 

That query replaces the home teams id with their name, but we have to do it again to replace the away teams id. The only difference is that as we are referring to the same table (teams) twice, we have to give each reference a name (t1 and t2 here) and use it to distinguish between column references that would otherwise be ambiguous. If we just said

 

SELECT * FROM results

INNER JOIN teams ON hometeam = teamsindex

INNER JOIN teams ON awayteam = teamsindex;the server wouldnt be able to figure out which reference to teams we are using each time we say "teamsindex", so we have to qualify the tables with aliases:

 

SELECT * FROM results

INNER JOIN teams AS t1 ON hometeam = t1.teamsindex

INNER JOIN teams AS t2 ON awayteam = t2.teamsindex;

 

Thanks go to Alan Larkin for this solution and explination.