CODEWITHSUNDEEP
sql
PJA
PJA

Reputation: 25

SQL ISNULL but also need results if nothing exists

I'm trying to write a query to export orders from my database. So far I can get it to pull the records if either a field in the 'extrafields' is null or has content but I am unable to figure out how to also include if no data exists in the 'extrafields' database I'd be very grateful if anybody can offer any help, here's what I have so far.

    SELECT 
    CASE WHEN ISNULL(e.content) THEN ol.code ELSE CONCAT(ol.code, ' ** ', e.content) END as `ItemNumber`,
CASE WHEN ISNULL(e.content) THEN ol.name ELSE CONCAT(ol.name, ' ** ', e.content) END as `ItemTitle`,
o.orderID AS orderId,
ol.productID AS orderItemId,
ol.qty AS Quantity,
ol.price + ol.taxamount AS CostPerUnit,  
'' AS ProductOption,
0 AS ItemTaxRate,
0 AS DiscountPercent,
ol.lineID
FROM
`jss_orders_headers` o,
`jss_orders_lines` ol

LEFT JOIN jss_orders_extrafields e ON e.lineID = ol.lineID
WHERE
e.content!=" " 
and ol.orderID = o.orderID 
AND e.extraFieldID NOT IN (130, 114, 113, 111, 84, 81)
and e.exValID > 0

Upvotes: 1

Views: 46

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

When you reference a left joined column (the columns from jss_orders_extrafields in your case) in the WHERE clause, you force the join to behave as if it were an INNER join. Instead, make those tests part of the join condition.

...
LEFT JOIN jss_orders_extrafields e 
    ON e.lineID = ol.lineID
        AND e.content != ' '
        AND e.extreaFieldID NOT IN (130, 114, 113, 111, 84, 81)
        AND e.exValID > 0
...

Also, be consistent in your join style and don't mix implicit and explicit joins.

...
FROM `jss_orders_headers` o
    INNER JOIN `jss_orders_lines` ol
        ON o.orderID = ol.orderID
...

And remove the o.orderID = ol.orderID test from the WHERE clause as well.

So, putting it all together:

SELECT CASE WHEN ISNULL(e.content) THEN ol.code ELSE CONCAT(ol.code, ' ** ', e.content) END as `ItemNumber`,
       CASE WHEN ISNULL(e.content) THEN ol.name ELSE CONCAT(ol.name, ' ** ', e.content) END as `ItemTitle`,
       o.orderID AS orderId,
       ol.productID AS orderItemId,
       ol.qty AS Quantity,
       ol.price + ol.taxamount AS CostPerUnit,  
       '' AS ProductOption,
       0 AS ItemTaxRate,
       0 AS DiscountPercent,
       ol.lineID
    FROM `jss_orders_headers` o
        INNER JOIN `jss_orders_lines` ol
            ON o.orderID = ol.orderID   
        LEFT JOIN jss_orders_extrafields e 
            ON e.lineID = ol.lineID
                AND e.content!=" " 
                AND e.extraFieldID NOT IN (130, 114, 113, 111, 84, 81)
                AND e.exValID > 0;

Upvotes: 3

Related Questions