Query with several consecutive LEFT JOIN - unexpected results

Jivan

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.

Gordon Linoff

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.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Showing results from Mysql Query that don't exist in LEFT JOIN

分類Dev

How to write a SQL query that subtracts INNER JOIN results from LEFT JOIN results?

分類Dev

left join in Linq query

分類Dev

how to order results of LEFT JOIN

分類Dev

Postgresql query with left join and having

分類Dev

MariaDB Left join not returning expected results

分類Dev

Hibernate criteria using left join to filter results

分類Dev

Sub query filter in LEFT/RIGHT JOIN

分類Dev

MySQL LEFT JOIN Query with WHERE clause

分類Dev

3 way left join query error

分類Dev

MySQL query Select, SUM, LEFT JOIN

分類Dev

mysql query with union and left join is slow

分類Dev

Problems with reusing LEFT JOIN results in WHERE and ORDER BY Clause

分類Dev

Updating sql table with results from left join (sql server)

分類Dev

SQL Query joining two tables similar to left outer join

分類Dev

Executing a query using sum, count, group by and multiple left join MySQL

分類Dev

MYSQL query SELECT from one table and join results with other table

分類Dev

Use Union or Join to use multiple query results in a where statement

分類Dev

How to Get Left Instead of Inner Join in Linq to SQL Navigation Property Generated Query

分類Dev

How do you multiple left join the same table from 2 different tables in the same query?

分類Dev

How can I optimize this incredibly slow left outer join sqlite query?

分類Dev

PHP displaying data from four tables in one query (ie: LEFT JOIN)

分類Dev

How to form complex mysql query that has left outer join, aggregate data with group by using SQLAlchemy?

分類Dev

MySQL LEFT JOIN with IF

分類Dev

Left Join SQL Server

分類Dev

Table Left Join

分類Dev

Left join returns blanks

分類Dev

Left join on not null

分類Dev

Slow result with LEFT JOIN

Related 関連記事

  1. 1

    Showing results from Mysql Query that don't exist in LEFT JOIN

  2. 2

    How to write a SQL query that subtracts INNER JOIN results from LEFT JOIN results?

  3. 3

    left join in Linq query

  4. 4

    how to order results of LEFT JOIN

  5. 5

    Postgresql query with left join and having

  6. 6

    MariaDB Left join not returning expected results

  7. 7

    Hibernate criteria using left join to filter results

  8. 8

    Sub query filter in LEFT/RIGHT JOIN

  9. 9

    MySQL LEFT JOIN Query with WHERE clause

  10. 10

    3 way left join query error

  11. 11

    MySQL query Select, SUM, LEFT JOIN

  12. 12

    mysql query with union and left join is slow

  13. 13

    Problems with reusing LEFT JOIN results in WHERE and ORDER BY Clause

  14. 14

    Updating sql table with results from left join (sql server)

  15. 15

    SQL Query joining two tables similar to left outer join

  16. 16

    Executing a query using sum, count, group by and multiple left join MySQL

  17. 17

    MYSQL query SELECT from one table and join results with other table

  18. 18

    Use Union or Join to use multiple query results in a where statement

  19. 19

    How to Get Left Instead of Inner Join in Linq to SQL Navigation Property Generated Query

  20. 20

    How do you multiple left join the same table from 2 different tables in the same query?

  21. 21

    How can I optimize this incredibly slow left outer join sqlite query?

  22. 22

    PHP displaying data from four tables in one query (ie: LEFT JOIN)

  23. 23

    How to form complex mysql query that has left outer join, aggregate data with group by using SQLAlchemy?

  24. 24

    MySQL LEFT JOIN with IF

  25. 25

    Left Join SQL Server

  26. 26

    Table Left Join

  27. 27

    Left join returns blanks

  28. 28

    Left join on not null

  29. 29

    Slow result with LEFT JOIN

ホットタグ

アーカイブ