LEFT JOIN is locking up mysql

[wspnut]

Hey all.  I'm running MySQL v4.1.20 at my company (yea, they're slow with upgrades) on PHP 4.4.7.

 

We run a normalized table (with InnoDB) and I have to span a few bridges once in a while with JOIN and WHERE statements.  The problem is, when I do the following JOIN statement, the system locks up and takes up 100% of the CPU:

 

 

SELECT crn.id, person.fname
FROM crn, instructor_crn
LEFT JOIN person ON (instructor_crn.cid = crn.id AND person.id = instructor_crn.person)
WHERE crn.id = '6'

 

However, running this statement works perfectly:

 

SELECT crn.id, instructor_crn.cid
FROM crn
LEFT JOIN instructor_crn ON (instructor_crn.cid = crn.id)
WHERE crn.id = '6'

 

Any help?  Thanks

 

--Andrew

[wspnut]

OK, well, just an FYI.  I got this working by doing the following code... but I assumed that the two would be equivalent.  I would REALLY prefer a statement similar to the first post, so any ideas would be much appreciated:

 

SELECT crn.id, person.fname
FROM crn
LEFT JOIN instructor_crn ON ( instructor_crn.cid = crn.id )
LEFT JOIN person ON ( person.id = instructor_crn.person )
WHERE crn.id = '6'

[bubblegum.anarchy]

Slight change:

 

SELECT crn.id, person.fname
FROM crn
     LEFT JOIN instructor_crn ON crn.id = instructor_crn.cid 
     LEFT JOIN person ON person.id = instructor_crn.person
WHERE crn.id = '6'

 

Should be fine... change the LEFT JOIN to INNER JOIN if a matching record must exist.

[btherl]

In general, you're safest to list your joins out like this:

 

SELECT * FROM t1
JOIN t2 ON (t1.field = t2.field)
JOIN t3 ON (t2.field = t3.field)
LEFT JOIN t4 ON (t3.field = t4.field)
WHERE ...

 

That makes it explicit how you are joining everything, and exactly where the left join should be done.  The way you wrote your problem query, you were putting the condition for a different join inside the ON clause for your left join, which probably baffled mysql.

[fenway]

OK, well, just an FYI.  I got this working by doing the following code... but I assumed that the two would be equivalent.  I would REALLY prefer a statement similar to the first post, so any ideas would be much appreciated:

 

SELECT crn.id, person.fname
FROM crn
LEFT JOIN instructor_crn ON ( instructor_crn.cid = crn.id )
LEFT JOIN person ON ( person.id = instructor_crn.person )
WHERE crn.id = '6'

 

Commas are EVIL! Never use them.