problem joining tables

Anon

Hi-
I have one table with two columns that references an ID# from a column in another table. I need help constructing an SQL statement that will match both columns from the first table with a column in the second table. Heres an example

table GIGS
gigMusician = 3 | gigAltMusician = 2 | gigDesc = "folk night"

table MUSICIANS
musicianID = 1 | musicianName = "bob dylan"
musicianID = 2 | musicianName = "joanie mitchel"
musicianID = 3 | musicianName = "joan baez"

I can get MUSICIANS.musicianID to join to GIGS.gigMusician, but not at the same time as GIGS.gigAltMusician. Is there a way to join the two columns in GIGS with the one in MUSICIANS?
(table GIGS has a key, but I didn't list it).
Any help would be appreciated. Thanks.

[deleted]

You can join the first table with two aliasses of the second table:

SELECT gigmusicians.name, gigaltmusicians.name
FROM GIGS,
MUSICIANS AS gigmusicians,
MUSICIANS AS gigaltmusicians
WHERE GIGS.gigMusician = gigmusicians.musicianID
AND GIGS.gigAltMusician = gigaltmusicians.musicianID

Anon

I can get this query to execute with my db:

$sql = "SELECT * FROM gigs, musicians AS gigmusicians, musicians AS gigaltmusicians WHERE gigs.gigMusicianID = gigmusicians.musicianID AND gigs.gigAlsoMusicianID = gigaltmusicians.musicianID";

And put the result in $row and loop through the recordset:

while($row = mysql_fetch_array($sql_result)) {

But now how can I reference the aliased table?
<?=$row[gigmusicians.musicianName]?> doesn't work.
<?=$row[gigaltmusicians.musicianName]?> doesn't work either.

Thanks for helping,
Christopher

Anon

simle example HOWTO use data from two (or more) tables

SELECT table1.field1 AS yourwish,
table2.field44 AS yourwish2
FROM table1, table2
WHERE table1.field1=table2.field44

Anon

Yes, but then how can I reference the aliased tables in my script?

[deleted]

By replacing the * with the fields you want, and giving those an alias too:

SELECT gigs.id as gigid,
gigmusicians.name as firstmusicianname,
gigaltmusicians.name as altmusiciansname
FROM gigs....

then in your code you get

echo $row['firstmusicianname'];

btw: always quote strings!!