MAX query return null even though have data

nurul 0
Viewed 32

I having a problem where a data doesn't listed(no affected row) when query with max command but without max command there is no problem(displays 2 rows), the data listed. Does anyone know what the root cause? I'm using 10.1.30-MariaDB This is the problem command;

SELECT ib.Id AS 'IB_RecID' 
FROM (csm_installbase ib
    LEFT JOIN systeminformation si1 ON ((si1.IB_RecID = ib.Id))
) 
WHERE (si1.Id IN (SELECT MAX(si3.Id) 
                  FROM systeminformation si3 
                  GROUP BY si3.IB_RecID))

The data listed without max command;

SELECT ib.Id AS 'IB_RecID' 
FROM (csm_installbase ib
        LEFT JOIN systeminformation si1 ON ((si1.IB_RecID = ib.Id))
    ) 
WHERE (si1.Id IN (SELECT si3.Id 
                  FROM systeminformation si3 
                    GROUP BY si3.IB_RecID)
)

I tried to use LIMIT but prompt with "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery" error box.

Thank you in advance for any help and effort.

mariadb
1 Answers

Test the next query:

SELECT t1.Id AS 'IB_RecID' 
FROM csm_installbase t1
JOIN systeminformation t2 ON t2.IB_RecID = t1.Id
JOIN ( SELECT MAX(Id) Id
       FROM systeminformation t3
       GROUP BY IB_RecID ) t4 ON t2.Id = t4.id

for your comment about LEFT JOIN and INNER JOIN. What should I replace? Actually the left join have 10+ from another table but skip it as not related. Should I adding INNER JOIN for it? – nurul

Your query formally is:

...
FROM t1
LEFT JOIN t2 ON {condition}
WHERE t2.column = {value}
...

What is LEFT JOIN?

It takes all rows from t1 and adds to each row the rows from t2 which matches {condition}. Then for each row from t1 which have no matched row in t2 it adds NULL into all according columns.

Then a condition from WHERE is applied, and column from t2 is compared with a value. But for the rows from t1 which have no matched row in t2 this will be WHERE NULL = {value}. This expression produces NULL which is treated as FALSE. And as a result - all such rows are removed from the rowset. Only rows from t1 which have matched rows in t2 may be returned. So the LEFT JOIN in this case acts as INNER JOIN. And this joining type may be edited.

If there are some other JOINs then you may investigate each of them using the same logic. Maybe some other outer joins are by fact INNER ones too.

Akina0
Related