Mysql return null if subquery returns null

Question

Hy guys, sometimes my subquery return null which is ok, it should return null, but in those cases i would like my "parent select" to return null.

Is that possible?

And if yes, then how?

Heres the code:

SELECT 
    `company`.`companyID`,
    `company`.`companyName`, 
    `company`.`companyName`, 
    `company`.`companyEmail`, 
    `company`.`contactEmail`,
    `company`.`companyTel`,                 
    (
        SELECT 
            `package_map`.`szekhely_endDate`
        FROM 
            `package_map` 
        WHERE 
            `package_map`.`companyID` = `company`.`companyID`
        AND 
            `package_map`.`active` = 1
        AND 
            `package_map`.`szekhely_endDate` > NOW()
        ORDER BY 
            `package_map`.`szekhely_endDate` DESC 
        LIMIT 1
    ) as endDate,
CASE 
    WHEN endDate = NULL

FROM 
    `company` 
WHERE 
    `company`.`companyBase` = 'some address' 
AND 
    `company`.`szekhely_check_out` = 0

JOIN with the subquery instead of putting it in the SELECT clause. — Barmar
– Barmar, Commented Mar 20, 2014 at 15:14

Barmar · Accepted Answer · 2014-03-20 15:27:54Z

2

Use an ordinary INNER JOIN between the two tables. If there's no matching rows in the package_map table, there won't be a row in the result. To get the latest endDate, use the MAX() function.

SELECT 
    `company`.`companyID`,
    `company`.`companyName`, 
    `company`.`companyName`, 
    `company`.`companyEmail`, 
    `company`.`contactEmail`,
    `company`.`companyTel`,                 
    MAX(package_map.szekhely_endDate) AS endDate
FROM company
INNER JOIN package_map ON `package_map`.`companyID` = `company`.`companyID`
WHERE 
    `company`.`companyBase` = 'some address' 
AND 
    `company`.`szekhely_check_out` = 0
AND 
    `package_map`.`active` = 1
AND 
    `package_map`.`szekhely_endDate` > NOW()
GROUP BY `company`.`companyID`

edited Mar 20, 2014 at 15:27

answered Mar 20, 2014 at 15:16

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr. Sam Over a year ago

i get Unknown column 'company.companyID' in 'where clause' error

Barmar Over a year ago

Oh, I didn't notice that it was a correlated subquery. I need to rethink this.

Mr. Sam Over a year ago

Ok please let me know if you got something

Collectives™ on Stack Overflow

Mysql return null if subquery returns null

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related