CODEWITHSUNDEEP
mysql
Oleg
Oleg

Reputation: 35

MySQL. Select with join where joined value not exists

I have 2 tables: element and element_property.

I want to select all elements where one of the properties is not exists. It means that this query returns nothing:

select * from element_property where PROPERTY_ID = 176 AND ELEMENT_ID = 123

but there is 1 row for:

select * from element where ID = 123

So, i want to find all elements for whom PROPERTY_ID = 176 is not exists.

This variant returns nothing:

select t1.ID, t2.* from element t1
left JOIN element_property t2 ON t1.ID = t2.ELEMENT_ID
where t2.PROPERTY_ID = 176 and t2.ID IS NULL

Upvotes: 1

Views: 2353

Answers (1)

xQbert
xQbert

Reputation: 35323

The problem you have is that the where clause having t2.property_ID =176 occurs after the join. Since elment ID 123 doens't have property 176 it gets excluded to the where clause.

By moving it to the join you're telling the engine to first filter all elment_properties to 176 then join it to element. Then eliminate all records which have that element (Where T2.ID is null)

your method negates the left join basically making it an inner join.

So if you update your select to this, it should work:

select t1.ID, t2.* 
from element t1 
left JOIN element_property t2 
  ON t1.ID = t2.ELEMENT_ID 
 and t2.PROPERTY_ID = 176 
where t2.Element_ID IS NULL

Lets focus on your where clause. Property_ID of 176 doesn't exist for element 123. So by saying where t2.property_ID = 176, you've excluded element 123.

By moving the limit to the JOIN your result set would look like this without a where...

Element Property ID
123     NULL     NULL 
003     NULL     NULL
001     176      1
002     176      2

So in this example by having a where property is null you would get records 123 and 003.

But if you said where property = 176 and ID is null... you'd get no records back.

Upvotes: 2

Related Questions