CODEWITHSUNDEEP
mysqlsqlsubqueryleft-join
Mawia HL
Mawia HL

Reputation: 3665

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
        LEFT JOIN ( 
                SELECT s.customerid, o.orderzat as ruakzat, 
                       f.filled as khatzat     
                FROM `sale` s    
                LEFT JOIN (     
                       SELECT SUM(quantity) as orderzat,invoiceno     
                       FROM `order`    
                       WHERE fillstatus='Empty'
                       GROUP BY invoiceno    
                 ) AS o ON s.invoiceno=o.invoiceno    
                LEFT JOIN (     
                      SELECT SUM(quantity) as filled,invoiceno    
                      FROM `order`    
                      WHERE fillstatus='Filled'    
                      GROUP BY invoiceno    
                ) AS f ON s.invoiceno=f.invoiceno    
        ) AS fd ON c.id=fd.customerid    
        LEFT JOIN (     
             SELECT SUM(quantity) as lended, customerid    
             FROM `lending`    
        ) AS puk ON c.id=puk.customerid     
       WHERE (puk.customerid IS NULL OR c.name LIKE '%%')

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

Upvotes: 0

Views: 982

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Not related to your problem, but you can replace those two join

        LEFT JOIN (     
               SELECT SUM(quantity) as orderzat,invoiceno     
               FROM `order`    
               WHERE fillstatus='Empty'
               GROUP BY invoiceno    
         ) AS o ON s.invoiceno=o.invoiceno    
        LEFT JOIN (     
              SELECT SUM(quantity) as filled,invoiceno    
              FROM `order`    
              WHERE fillstatus='Filled'    
              GROUP BY invoiceno

with a single one using Conditional SUM 

        LEFT JOIN (     
               SELECT invoiceno, 
                      SUM(CASE WHEN fillstatus='Empty' THEN quantity
                                                       ELSE 0
                          END) as orderzat,     
                      SUM(CASE WHEN fillstatus='Filled' THEN quantity
                                                        ELSE 0
                          END) as filled     
               FROM `order`    
               GROUP BY invoiceno    
         ) AS o ON s.invoiceno=o.invoiceno

Upvotes: 1

Shadow
Shadow

Reputation: 34231

MySQL has a few bug reports around similar issues (https://bugs.mysql.com/bug.php?id=31450 , https://bugs.mysql.com/bug.php?id=35633 , https://bugs.mysql.com/bug.php?id=52441), 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.

Upvotes: 2

Related Questions