Query with several consecutive LEFT JOIN - unexpected results
Let's say I have 1 table A (parent) plus 5 other tables B, C, D, E, F
(childs linked to A by a foreign key on their id's). The goal of my query
is to populate several objects, which have the same structures as the db
tables, all at once. One of the conditions for a record to be returned
needs the query to check values in some other tables, such as G and H (B
and C having foreign keys respectively in G and H).
I have this query:
SELECT
A.*,
B.*,
C.*,
D.*,
E.*,
F.*,
FROM A
LEFT JOIN B ON A.id = B.id
LEFT JOIN C ON A.id = C.id
LEFT JOIN D ON A.id = D.id
LEFT JOIN E ON A.id = E.id
LEFT JOIN F ON A.id = F.id
LEFT JOIN G ON B.myfield = G.myfield
LEFT JOIN H ON C.myfield = H.myfield
WHERE G.anotherfield IN (myvalue1);
The idea is that the WHERE clause is dynamically generated with PHP, so it
can look for things in the different tables. This example looks for a
record where, in the G row where G.myfield has the same value as B.myfield
(for the B row where B.id equals A.id), G.anotherfield is equal to
myvalue1.
My idea would be to do a WHERE clause being like:
WHERE G.anotherfield IN (myvalue1, myvalue2)
OR H.anotherfield IN (myvalue3, myvalue4)
OR F.onemorefield IN (myvalue5, myvalue6, myvalue7)
etc.
And then, fetch the different records into my child objects, each record
creating an instance of the correct child class (the child class to
instantiate being identified by the field type in table A).
But let's stick with just one WHERE clause for the moment.
What I would expect, is that in this sample case, the returned value(s)
are filled with the fields of table A and table B. But it occurs that
whereas I get the expected results in terms of rows (the returned rows are
the ones I want to be returned), the records are filled only with table
A's fields, and not table B's.
For instance, let's say my object is stored in tables A and B like this:
Table A
id type created
------------------------------
392 B 1377084886
Table B
id myfield myotherfield
----------------------------------
392 234 foo
What I want to get is this:
id->392
type->B
created->1377084886
myfield->234
myotherfield->foo
What I get instead is:
id->392
type->B
created->1377084886
myfield->NULL
myotherfield->NULL
Now, if I remove all the unnecessary left joins, and only leaves LEFT JOIN
B and LEFT JOIN G, then everything works fine for this particular example.
I don't understand why, when I put back in place the other joins, I get
the result I have.
Any idea?
No comments:
Post a Comment