Mawia HL Mawia HL - 1 year ago 116
SQL Question

Mysql Column cannot be null error when there is no result using LEFT JOIN

I have some data in customer table and there is no data in the other tables. So when I am trying to select using the following query, it gives me

1048-Column 'customerid' cannot be null

. What could be the problem. Is there a solution for this?

SELECT c.*, fd.ruakzat as ruak,
fd.khatzat as khat, 0+0 as belh, 0+0 as neih,
puk.lended as hawh
FROM `customer` c
SELECT s.customerid, o.orderzat as ruakzat,
f.filled as khatzat
FROM `sale` s
SELECT SUM(quantity) as orderzat,invoiceno
FROM `order`
WHERE fillstatus='Empty'
GROUP BY invoiceno
) AS o ON s.invoiceno=o.invoiceno
SELECT SUM(quantity) as filled,invoiceno
FROM `order`
WHERE fillstatus='Filled'
GROUP BY invoiceno
) AS f ON s.invoiceno=f.invoiceno
) AS fd ON
SELECT SUM(quantity) as lended, customerid
FROM `lending`
) AS puk ON
WHERE (puk.customerid IS NULL OR LIKE '%%')

What would be the better approach? Thanks for your help.

Answer Source

MySQL has a few bug reports around similar issues ( , ,, so it is possible that you encountered the same bug.

To sum it up: if the field in question is non-nullable by definition (defined as not null in the create table), but appears in a subquery where the output result may be null.

Pls check your MySQL version because it may be such a version where one of the above listed bugs do appear.

Furthermore, to me the subquery

         SELECT SUM(quantity) as lended, customerid    
         FROM `lending`    

does not make too much sense, since there is no group by part. This will collapse the lending table into a single record, with customerid randomly chosen from one of the records. So, I would add a group by customerid to the above subquery.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download