I found a view using a "bad practices example" query and I can't understand the results it gives.
The model is as following :
- an object has contacts (1..+inf).
- an object has a unique classification_code
- a contact may have addresses (0..+inf)
- an object may have descriptions (0..+inf)
- a description has a type
The query aims to retrieve all the objects with their address and a description of a given type (ie : 1234).
Here's the current query :
select o.id_object, o.type, ca.address1, ca.address2, d.description
from contacts c
left join contacts_addresses ca on c.id_contact = ca.id_contact
join objects o on o.id_object = c.id_object
left join descriptions d on d.id_object = c.id_object and d.type = '1234' and o.type = 'abcd'
My problem stands in the last criteria (bold) : I expected this query to retrieve only the objects of type "abcd" insteads of all the objects.
But it doesn't affect the result. Either I put it or not, I get all the objects.
I don't know why descriptions
has been joined on contacts
and not objects
but I don't think it's important. And, of course, I also don't know why this last criteria is here !
Is anyone able to explain this ?