[Resolved] [Resolved] Utilizing a primary key twice

La_Parka

I'm making a site for my fantasy basketball league that uses PHP-MySQL for backend, and I am creating a page that lists player transactions (e.g. drop one player from your roster, sign a player to take his spot.) I made a table for transactions that has one field for the player that has been cut ("cutid"), and one for the player that is signed to take their place ("signid"), and uses "transactionid" as a primary key. The cut/sign fields link to a table that I have for players. (check attachment)

Is there a way, using only one query, to print out the following data (as per the attachment):

transaction || sign || cut
1 || Quentin Richardson || Tim Duncan
2 || Keith Van Horn|| Quentin Richardson

Is it possible to relate both "signid" and "cutid" to the playerid field?

drawmack

SELECT a.transactionid, b.fname, b.lname, c.fname, c.lname FROM transaction as a, players as b, players as c where a.signid = b.playerid AND a.cutid = c.playerid

I think this will do it, but it may not. I would recommend running this query first as a test.

SELECT count(a.transactionid) FROM transaction as a, players as b, players as c where a.signid = b.playerid AND a.cutid = c.playerid and a.transactionid = 1

If this query returns more then 1 then my where clause is messed up, otherwise my query will work.

La_Parka

Originally posted by drawmack
SELECT a.transactionid, b.fname, b.lname, c.fname, c.lname FROM transaction as a, players as b, players as c where a.signid = b.playerid AND a.cutid = c.playerid

I think this will do it, but it may not. I would recommend running this query first as a test.

SELECT count(a.transactionid) FROM transaction as a, players as b, players as c where a.signid = b.playerid AND a.cutid = c.playerid and a.transactionid = 1

If this query returns more then 1 then my where clause is messed up, otherwise my query will work.

I got it to work using your exact code. Thanks for the help!