Re: Strange result from multiple JOIN
I'll defer to you. At 01:00 PM 5/28/2007, Baron Schwartz wrote: >Dave Dyer wrote: >>Thanks, it turns out you are exactly right. I rewrote >>the query to keep the "on" immediately following the "left join" >>and it now works as I wish. >>I'll have to read up on "cross join", but if there >>is a mysql bug here, it is that the parser that what I wrote as "left join" >>was turned into a cross join. >>-- the second "on" clause in a row ought to have triggered >>a complaint. > >Yes, I too thought that should be an error. I have tried to figure out >whether it's illegal to have two in a row or to omit an ON or USING clause, >but I can't convince myself completely by referring to >http://dev.mysql.com/doc/refman/5.0/en/join.html. I'm not sure I am reading >it correctly. But it does seem the parser should reject it. > >If you want to pursue this further I won't duplicate your effort, but I'm >happy to dig deeper on it and see if there is a bug. > >Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange result from multiple JOIN
Dave Dyer wrote: Thanks, it turns out you are exactly right. I rewrote the query to keep the "on" immediately following the "left join" and it now works as I wish. I'll have to read up on "cross join", but if there is a mysql bug here, it is that the parser that what I wrote as "left join" was turned into a cross join. -- the second "on" clause in a row ought to have triggered a complaint. Yes, I too thought that should be an error. I have tried to figure out whether it's illegal to have two in a row or to omit an ON or USING clause, but I can't convince myself completely by referring to http://dev.mysql.com/doc/refman/5.0/en/join.html. I'm not sure I am reading it correctly. But it does seem the parser should reject it. If you want to pursue this further I won't duplicate your effort, but I'm happy to dig deeper on it and see if there is a bug. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange result from multiple JOIN
Thanks, it turns out you are exactly right. I rewrote the query to keep the "on" immediately following the "left join" and it now works as I wish. I'll have to read up on "cross join", but if there is a mysql bug here, it is that the parser that what I wrote as "left join" was turned into a cross join. -- the second "on" clause in a row ought to have triggered a complaint. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange result from multiple JOIN
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]
Strange result from multiple JOIN
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
