CODEWITHSUNDEEP
mysqljoinleft-join
Jivan
Jivan

Reputation: 23068

Query with several consecutive LEFT JOIN - unexpected results

My goal is to populate several objects of several child classes (B, C, D, E, F...), all extending a parent class which is A. All of this, in one single and only big query.

Let's say I have several tables reflecting the classes structure, including these 3 ones:

Table A /* the parent class */
id       type        created
--------------------------------
392      B           1377084886

Table B /* one of the child classes */
id       myfield     myotherfield     anotherone    oneagain
-------------------------------------------------------------
392      234         'foo'            'bar'         3

Table G /* not part of the structure, just here for the query to check a field */
myfield      fieldtocheck       evenmorefields
------------------------------------------------
234          'myvalue1'         'foobar'

Now:

/* This (the query I want): */
SELECT
    a.*,
    b.*,
    c.*,
    d.*,
    e.*,
    f.*
    FROM A a
    LEFT JOIN B b ON a.id = b.id
    LEFT JOIN C c ON a.id = c.id
    LEFT JOIN D d ON a.id = d.id
    LEFT JOIN E e ON a.id = e.id
    LEFT JOIN F f ON a.id = f.id
    LEFT JOIN G g_b ON b.myfield = g_b.myfield
    LEFT JOIN G g_c ON c.myfield = g_c.myfield
    WHERE g_b.fieldtocheck IN (myvalue1);

/* Returns this (what I don't want): */
id->392
type->B
created->1377084886
myfield->NULL /* why NULL? */
myotherfield->NULL /* why NULL? */
anotherone->NULL /* why NULL? */
oneagain->3 /* why, whereas other fields from B are NULL, is this one correctly filled? */

Whereas:

/* This (the query I don't want): */
SELECT
    a.*,
    b.*
    FROM A a
    LEFT JOIN B b ON a.id = b.id
    LEFT JOIN G g_b ON b.myfield = g_b.myfield
    WHERE g_b.fieldtocheck IN (myvalue1);

/* Returns this (what I want): */
id->392
type->B
created->1377084886
myfield->234
myotherfield->'foo'
anotherone->'bar'
oneagain->3

I have no idea why. Tried different things, but this is what I come up with. Has someone an idea?

EDIT: Clarified this post and made it more straightforward.

Upvotes: 3

Views: 5125

Answers (3)

suppi
suppi

Reputation: 31

SELECT a.id, a.type, a.created, b.myfield, b.myotherfield FROM `A` AS a INNER JOIN `B` AS b ON a.id = b.id ;

Use this It must work fine. If you have an additional where query you can add it.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think the problem that you are facing is the collision of names in the query. That is, there are multiple columns with the same name and MySQL chooses one of them for the result.

You need to alias the column names for each of the tables to a different name, for example, a_created, b_created, etc.

Upvotes: 2

jenson-button-event
jenson-button-event

Reputation: 18951

This appears to be the classic case where you need to move your where into the join itself so that it doesn't block everything else:

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 and G.anotherfield IN (myvalue1)
    LEFT JOIN H ON C.myfield = H.myfield;

Upvotes: 1

Related Questions