[RESOLVED] Three-table join problem/question

[Ryodox]

Having a bit of trouble here.

$query = "
SELECT transactions.`trans_date` , transactions.`trans_content` , transactions.`trans_status` , users.`user_name` , users.`user_url`
FROM `trans_proj_assoc`
JOIN (
`transactions`
CROSS JOIN `users`
) ON ( transactions.`trans_id` = trans_proj_assoc.`trans_id`
AND (
transactions.`trans_send_id` = users.`user_id`
OR transactions.`trans_rcv_id` = users.`user_id`
) )
WHERE trans_proj_assoc.`proj_id` = ".$proj_id;

This query produces the following:

trans_date 	trans_content 	trans_status 	user_name 	user_url
2009-05-13 	tech 	Pending 	admin 	999999
2009-05-12 	money 	Completed 	admin 	999999
2009-05-13 	tech 	Pending 	user 	1
2009-05-12 	money 	Completed 	user 	1

However, what I need to happen is, instead of a single user_name and user_url field being returned per row, and the rows being duplicated, I need to return user_name and user_url twice in the row, for trans_send_id and trans_rcv_id (which match user_id's in the users table). How could I fix this?

[Ryodox]

Never mind, I realized I had to join the users table twice with aliases:

$query = "
		SELECT
			transactions.`trans_date`,
			transactions.`trans_content`,
			transactions.`trans_status`,
			send.`user_name` AS `send_name`,
			send.`user_url` AS `send_url`,
			rcv.`user_name` AS `rcv_name`,
			rcv.`user_url` AS `rcv_url`
		FROM `trans_proj_assoc`
		JOIN
		(
			`transactions`
			JOIN
				`users` AS `send`
			ON
				transactions.`trans_send_id` = send.`user_id`
			JOIN
				`users` AS `rcv`
			ON
				transactions.`trans_rcv_id` = rcv.`user_id`
		)
		ON
		(
			transactions.`trans_id` = trans_proj_assoc.`trans_id`
		)
		WHERE trans_proj_assoc.`proj_id` = ".$proj_id;