mysql - Magento MySQL 查询多左连接 - 语法错误

Question

因此，Magento 具有 EAV 表结构，这使得编写查询非常有趣。我正在尝试将与唯一客户相关的几行数据与另一个包含有关该客户的其他数据的表相匹配。

为了做到这一点，我目前正在尝试在一个查询中对五个表进行 LEFT JOIN，并为单个客户构建一个结果集。

表结构如下:

客户实体表

customer_entity_varchar

+----+------------+--------------+----------+
| id | entity_id  | attribute_id |   value  |
+----+------------+--------------+----------+
|  2 | 1          | 100          | Bob Doe  |
|  3 | 1          | 101          | James    |
|  5 | 1          | 102          | Thompson |
|  7 | 2          | 100          | Bob Doe  |
|  9 | 2          | 101          | Mary     |
+----+------------+--------------+----------+

客户定价表

pricesystem_customerprice

+----+------------+--------------+----------+-------+------------+
| id | entity_id  |  customer_id |   value  |  qty  |     to     |
+----+------------+--------------+----------+-------+------------+
|  2 | 35         | 1            | 34.99    |  10   | 2014-06-18 |
|  3 | 3495       | 2            | 123.99   |  5    | 2014-06-18 |
+----+------------+--------------+----------+-------+------------+

目录产品表

catalog_product_entity

+----+------------+----------------+
| id | entity_id  |       sku      |
+----+------------+----------------+
|  2 | 35         | Fenix-E01-Blue |
|  3 | 3495       | Sunwayman-V11R |
+----+------------+----------------+

我的查询如下:

SELECT 
    t1.`value` as account_manager, 
    t2.`value` as lastname, 
    t3.`value` as firstname,
    s.sku as item,
    c.`value` as price,
    c.qty as quantity,
    c.`comment` as `comment`
FROM 
    customer_entity_varchar as t1 
        on c.customer_id = t1.entity_id AND t1.attribute_id = 286
left join 
    customer_entity_varchar as t2
        on c.customer_id = t2.entity_id AND t2.attribute_id = 5
left join
    customer_entity_varchar as t3 
        on c.customer_id = t3.entity_id AND t3.attribute_id = 7 
left join 
    pricesystem_customerprice as c 
        on c.customer_id = t2.entity_id
left join 
    catalog_product_entity as s 
        on s.entity_id = c.entity_id 
WHERE c.`to` = '2014-06-18'

语法似乎有问题，但是我不太精通连接表，所以我可能输入了错误的内容。我确实对此进行了一些研究，并尽我所能。

如果我不再需要获取客户的名字和姓氏，那么我可以使语句连接两个表。

这里是想要的结果集:

Array (
0 => array (
    account_manager => Bob Doe,
    firstname => James,
    lastname => Thompson,
    item => Fenix-E01-Blue,
    price => 34.99,
    quantity => 10),
1 => array (
    account_manager => Bob Doe,
    firstname => Mary,
    lastname => Thompson,
    item => Sunwayman-V11R,
    price => 123.99,
    quantity => 5)
)

如果您需要任何其他信息，请告诉我。

Answer 1

你的连接有点乱，你需要的是选择一个基表作为原点(这里我选择了产品表)并将所有内容左连接到它；

SELECT 
    t1.`value` as account_manager, 
    t2.`value` as lastname, 
    t3.`value` as firstname,
    s.sku as item,
    c.`value` as price,
    c.qty as quantity
FROM pricesystem_customerprice as c 
LEFT JOIN customer_entity_varchar as t1 
  ON c.customer_id = t1.entity_id AND t1.attribute_id = 100
LEFT JOIN customer_entity_varchar as t2
  ON c.customer_id = t2.entity_id AND t2.attribute_id = 101
LEFT JOIN customer_entity_varchar as t3 
  ON c.customer_id = t3.entity_id AND t3.attribute_id = 102
LEFT JOIN catalog_product_entity as s 
  ON s.entity_id = c.entity_id 
WHERE c.`to` = '2014-06-18'

此外，您对每个连接的条件应该只引用在当前连接之前已经连接的表，而不是在当前连接下方的连接中列出的表。

A simple SQLfiddle to test with .