MySQL all from first table, exist from second-CodePudding

I have a problem with a MySQL query. I have two tables:

table1

id
name
status

table2

plu (id from table1)
nr_order
quantity
txt

I would like to show all the rows from table1 and only the ones that match from table2. I have this query:

$selectNumberOrder = $this->__db->execute("SELECT ID_order 
                                            from order 
                                            WHERE FK_ID_shop = x 
                                            AND data = 'xxx' 
                                            LIMIT 1;");

$selectNumberOrder_ = $selectNumberOrder[0]['ID_Order'];
    

SELECT table1.id, table1.name, table2.quantity, table2.txt 
from table1
    LEFT JOIN table2 ON table1.id = table2.plu
WHERE table1.status = 1 
AND table2.nr_order = {$selectNumberOrder_} 
ORDER BY table1.id");

but the query output only shows the elements that are in table2. I do not know how to make it show all the elements from table1 and add the elements from table2 to them (if table2 does not have the id data from table1, let the quantity be empty)

CodePudding user response：

As written in my comment, you are using a column of table2 in the where clause. Leaving that out will resolve the error. If you still need to have that Where clause in place you should chain it with a NULL check.

Like so:

SELECT table1.id, table1.name, table2.quantity, table2.txt 
from table1 
    LEFT JOIN table2 ON table1.id = table2.plu
WHERE table1.status = 1 
AND (table2.nr_order IS NULL OR table2.nr_order = {$selectNumberOrder_}) 
ORDER BY table1.id");

CodePudding user response：

In LEFT JOIN all conditions by right table (table2 in your case) must be placed not into WHERE clause but into ON clause.

SELECT table1.id, table1.name, table2.quantity, table2.txt 
FROM table1
LEFT JOIN table2 ON table1.id = table2.plu
                AND table2.nr_order = {$selectNumberOrder_} 
WHERE table1.status = 1 
ORDER BY table1.id;

In this case server firstly selects a subset of right table which matches the condition table2.nr_order = {$selectNumberOrder_} and only then joins this subset to the left table using table1.id = table2.plu.

PS. Of course this is not correct but you may think so.