[RESOLVED] can I have two clauses in the join condition?

sneakyimp

This query runs fine on MySQL 4.1.22.

SELECT pc.id AS pendingChangeID, pc.pending_change_type as changeType, pc.foreign_key as changedItemID, pc.submitted_on as submittedOn, pc.submitted_by as accountID, a.username as username, IFNULL(c.name, CONCAT(p.first_name, ' ', p.last_name)) as changedItemName
	FROM pending_changes pc, account a
	LEFT JOIN company c
	ON (pc.pending_change_type='COMPANY') AND (c.id=pc.foreign_key)
	LEFT JOIN person p
	ON (pc.pending_change_type='PERSON') AND (p.id=pc.foreign_key)
	WHERE a.id=pc.submitted_by

However, on MySQL 5.0.22 it reports this error:

Error Code : 1054
Unknown column 'pc.pending_change_type' in 'on clause'

The field does in fact exist. What's the problem here?

tomhath

I don't see how that query would ever return any rows.

How can a row in pending_changes have pending_change_type = 'PERSON' and 'COMPANY' at the same time?

mpb001

The query executor has been 'fixed' in 5 and your error is to do with the order of the clauses.

Don't know what line is causing it but probably

pending_changes pc, account a

try swapping them to account a , pending_changes pc
so it reads now reads

 pending_changes pc
 LEFT JOIN company c ON

before you were left joining onto account a.

sneakyimp

Adding some parentheses around those first two tables apparently helps:

SELECT pc.id AS pendingChangeID, pc.pending_change_type as changeType, pc.foreign_key as changedItemID, pc.submitted_on as submittedOn, pc.submitted_by as accountID, a.username as username, IFNULL(c.name, CONCAT(p.first_name, ' ', p.last_name)) as changedItemName
	FROM (pending_changes pc, account a)
	LEFT JOIN company c
	ON (pc.pending_change_type='COMPANY') AND (c.id=pc.foreign_key)
	LEFT JOIN person p
	ON (pc.pending_change_type='PERSON') AND (p.id=pc.foreign_key)
	WHERE a.id=pc.submitted_by

WTF?

Piranha

The best solution is to always use join when you want an inner join. Don't mix JOIN with several tables in FROM.

SELECT pc.id AS pendingChangeID, pc.pending_change_type as changeType, pc.foreign_key as changedItemID, pc.submitted_on as submittedOn, pc.submitted_by as accountID, a.username as username, IFNULL(c.name, CONCAT(p.first_name, ' ', p.last_name)) as changedItemName
	FROM pending_changes pc
JOIN account a ON a.id=pc.submitted_by
	LEFT JOIN company c
	ON (pc.pending_change_type='COMPANY') AND (c.id=pc.foreign_key)
	LEFT JOIN person p
	ON (pc.pending_change_type='PERSON') AND (p.id=pc.foreign_key)

sneakyimp

thanks for tip!