Geek asks and answers

MySQL left the connection, fewer results

I have a little problem, I'm stuck. I have two tables. The first contains all id, the second contains some, but not all of tableA, the value and the corresponding identifier. Since I thought I understood, the left join on id should give me all id in the first table, and null for the second if id does not exist in tableB. But I keep getting the id number that exists in both tables. I misunderstood the concept?

My statement so far:

SELECT tableA.id, tableB.desiredValue
FROM tableA 
LEFT JOIN tableB ON tableA.id=tableB.item_id
WHERE tableB.element_id = 'something'
OR tableB.element_id IS NULL;

Any pointers?

Thanx, Best Regards, Marcus

+3
source share
3 answers

Move a condition that includes a column tableBfrom WHEREto ON:

SELECT tableA.id, tableB.desiredValue
FROM tableA 
LEFT JOIN tableB ON  tableA.id=tableB.item_id
                 AND tableB.element_id = 'something'

--- WHERE tableB.element_id IS NULL        --- this is probably not appropriate
;
+1
source

try it

SELECT tableA.id, tableB.desiredValue
FROM tableA 
LEFT JOIN tableB ON tableA.id=tableB.item_id
AND (tableB.element_id = 'something'
OR tableB.element_id IS NULL);
0

, , . where:

SELECT tableA.id, tableB.desiredValue
FROM tableA 
LEFT JOIN tableB ON tableA.id = tableB.item_id
WHERE tableB.element_id = 'something' OR tableB.item_id IS NULL;

I replaced it item_idas a string, which I check on the NULLinside, although it should matter if element_idit cannot be NULLin the lines where the connection works.

Can you talk about why you need the other part of the where - clause tableB.element_id = 'something'? My general recommendation is if you just want all returned rows to completely exclude the WHERE clause.

Can you explain a little more what are the criteria for the rows you want to return at all?

0
source

More articles:


All Articles