join one table twice to differnt colum

[leatherback]

Hey all,

I am building a squash tournament module. For this, I store the matches and players. As a consquence, you have a column with challenger and challenged ID. I would like to join the user tables with these colums. This is my query:

select ga_date, ga_s1c, ga_s1d, ga_s2c, ga_s2d, ga_s3c, ga_s3d, ga_winner, 
        challenger.pe_name as challenger_name,  

	challenged.pe_name as challenged_name 
	from games
            left join people as challenger 
			       on ga_challenger = challenger.pe_id 
            left join people as challenged 
			       on ga_challenged = challenged.pe_id 
       order by ga_date, challenger.pe_id, challenged.pe_id

This results in output:

ga_date ga_s1c ga_s1d ga_s2c ga_s2d ga_s3c ga_s3d ga_winner challenger_name challenged_name
2007-01-18 19:59:24 2 9 9 1 8 9 0 Jelle Ferwerda NULL

Why do I get a NULL for challenged_name? (The table holds a valid value) and how do I solve this?

Any ideas?

:queasy:

[Roger_Ramjet]

Well, normally with the LEFT join that would indicate no match for the id entered in the challenger column, perhaps because it is empty, otherwise because it is not a valid one.