Left join from SQL

tonelm54
tonelm54 used Ask the Experts™
on
Ive got an SQL query which is working great:-
SELECT `paras`.`paraName`, `call`.`priID`
FROM `paras`
LEFT JOIN `call`
ON `paras`.`paraName` = `call`.`assignedTo`
ORDER BY `paras`.`paraName` ASC

Open in new window


Problem is I now only want it to look only in `call` where `cleared`=null or 0000-00-00 00:00:00, so something like:-
SELECT (SELECT * FROM `call` WHERE `timeClear` IS NULL or `timeClear` = '0000-00-00 00:00:00'; ) AS `paraName`, `call`.`priID`
FROM `paras`
LEFT JOIN `call`
ON `paras`.`paraName` = `call`.`assignedTo`
ORDER BY `paras`.`paraName` ASC

Open in new window


So need to do a subquery and pull the LEFT JOIN.

Im a little confused how I can do this, does anyone have any ideas or pointers?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Ive put a quick example on sqlfiddle at http://sqlfiddle.com/#!2/20bc9/1 in the hope someone with more experience can help.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I suspect you just need to use "join conditions"
SELECT
	  `paras`.`paraName`
	,`call`.*

FROM `paras`

LEFT JOIN `call` ON `paras`.`paraName` = `call`.`assignedTo`
	AND `timeClear` IS NULL
	OR `timeClear` = '0000-00-00 00:00:00'

ORDER BY `paras`.`paraName` ASC
;

Open in new window

but in trying to use that sqlfiddle I get this error message:
Cannot convert value '0000-00-00 00:00:00' from column 8 to TIMESTAMP.: SELECT `paras`.`paraName` ,`call`.* FROM `paras` LEFT JOIN `call` ON `paras`.`paraName` = `call`.`assignedTo` AND `timeClear` IS NULL OR `timeClear` = '0000-00-00 00:00:00' ORDER BY `paras`.`paraName` ASC
SELECT	`paras`.`paraName`
,	`call`.`priID`
FROM	`paras`
LEFT
JOIN	`call`			ON	`paras`.`paraName` = `call`.`assignedTo`
				AND	(	`timeClear` IS NULL
					OR	`timeClear` = 0
					)
ORDER
BY	`paras`.`paraName` ASC

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial