Left join from SQL

Posted on 2014-08-05
Last Modified: 2014-08-27
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
Question by:tonelm54

    Author Comment

    Ive put a quick example on sqlfiddle at!2/20bc9/1 in the hope someone with more experience can help.
    LVL 47

    Accepted Solution

    I suspect you just need to use "join conditions"
    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
    LVL 11

    Assisted Solution

    SELECT	`paras`.`paraName`
    ,	`call`.`priID`
    FROM	`paras`
    JOIN	`call`			ON	`paras`.`paraName` = `call`.`assignedTo`
    				AND	(	`timeClear` IS NULL
    					OR	`timeClear` = 0
    BY	`paras`.`paraName` ASC

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now