Dave Dyer wrote:
I'm trying to construct a join, but the effect I want seems
to be impossible to achieve. In this schema, the "uid"
field is unique in the "players" table, but not in the "ranking"
table (one player per uid, multiple rankings per player)
I want to select player names and rankings for a particilar game
SELECT player_name,players.uid,e_mail,ranking.value from participant
left join players left join ranking
on participant.pid = players.uid
on participant.pid = ranking.uid
where tid='7' and ranking.variation='zertz'
This works perfectly if the ranking table actually exists, but if
there is no ranking table, the entire row is filtered out by the
"and ranking.variation='zertz'" since that is nulled the join.
If I make the where clause
where tid='7' and (ranking.variation='zertz' or ranking.variation is
null)
Instead of getting one row per UID, I get many. Appently
instead of getting N rows (one per uid) I get N^2 rows, N per UID.
Is there a trick I'm missing? Is this a join bug?
I doubt it's a bug. I think you have probably created a bug by creating
a cross join without realizing it. Try to begin with just one table,
then add just one LEFT JOIN, then another.
I am a bit confused by some of your terminology, such as "if there is no
ranking table." If there's no ranking table, the server will throw a
"no such table" error and refuse to execute the statement. I think you
mean "if the table is empty."
You may be confusing yourself with your JOIN syntax, which I would have
said is not even legal, but I just tried it and it doesn't complain :-)
But, without trying it myself, may I suggest you try something more
like this:
SELECT player_name,players.uid,e_mail,ranking.value
from participant
left join players on participant.pid = players.uid
left join ranking on participant.pid = ranking.uid
where tid='7' and ranking.variation='zertz'
Notice I place the ON clause right after each JOIN. I think this is
probably going to remove the cross-join you might have written without
knowing it.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
