extracting data from 2 tables

building the right mysql query?

[raulwg]

Hi there

my problem is that i want to extract data from 2 tables. The first table, called club, has among others the colomnames: C_id and C_name. The second table, called match, consists of M_id, C_id_home and C_id_out.

Now I want to show the matches from match where C_id_home and C_id_out correspond with club.C_id so that I can show the club by name instead of by (id)number.

This is my code so far:

SELECT * FROM match, club
WHERE match.C_id_home = club.C_id
AND match.C_id_out = club.C_id
ORDER BY match.M_id DESC

but this doesn't work properly. In the end I want to show a list that looks like this:

C_name(C_id_home) - C_name(C_id_out)

Any solutions?

Thanks in advance
Raul

[Dijkgraaf]

Because you are trying to join to the club table twice (once for home and one for out), you need to alias that table, something like.

SELECT clubhome.C_name, clubout.C_name
FROM match, club clubhome, club clubout
WHERE match.C_id_home = clubhome.C_id
AND match.C_id_out = clubout.C_id
ORDER BY match.M_id DESC

Notice how I have the club twice in the FROM statement, each followed by a new name for that table.

[raulwg]

Thanks for your reaction, but it won't work, unless I don't use the right code to print the rows, like:

echo $row_match["clubhome.C_name"];

[Dijkgraaf]

Are you saying
echo $row_match["clubhome.C_name"];
doesn't work?

You can also alias the result fields.

SELECT clubhome.C_name homeclub, clubout.C_name outclub
FROM match, club clubhome, club clubout
WHERE match.C_id_home = clubhome.C_id
AND match.C_id_out = clubout.C_id
ORDER BY match.M_id DESC

Then you can have

echo $row_match["homeclub"];
and
echo $row_match["outclub"];

[raulwg]

okeej! Thanks a lot, it worked just fine like this. So cool, your quick reaction on this one!

Greetz Raśl