[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Left join from SQL

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
0
tonelm54
Asked:
tonelm54
2 Solutions
 
tonelm54Author Commented:
Ive put a quick example on sqlfiddle at http://sqlfiddle.com/#!2/20bc9/1 in the hope someone with more experience can help.
0
 
PortletPaulCommented:
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
0
 
John_VidmarCommented:
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

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now