php - 复杂的 MySQL 查询正确连接表

Question

我有 3 个相互关联的表，我正在尝试正确查询数据，以便获得所需的一切。

这是我有的 3 个表:

订单商品

+------------+--------------+------+-----+
| order_id   | product_id   |count | max |
+------------+--------------+------+-----+
| 1AB45      | 3214         | 1    | 20  |
| YE72H      | 314          | 2    | 20  |
| HWYE1      | 5311         | 3    | 10  |
| HWYE1      | 314          | 1    | 20  | 
+------------+--------------+------+-----+

订单

+-----------+-------------+---------+-----+
| order_id  | buyer_email | user_id | ... |
+-----------+-------------+---------+-----+
|  1AB45    | user1@gmail | 123     |     |
|  YE72H    | user2@al.uk | 124     |     |
|  HWYE1    | me@mail.com | 125     |     |
+-----------+-------------+---------+-----+

reg_keys

+-----------+-------------+---------+------------+
| reg_key   | time_redeem | user_id | product_id |
+-----------+-------------+---------+------------+
|  UN-12XZ  | 2015-11-12  | 123     |    3214    |
|  UN-34AB  | 2015-10-12  | 125     |    5311    |
|  UN-67UY  | 2015-09-12  | 125     |    314     |
+-----------+-------------+---------+------------+

我希望获取所有计数/数量等于 1 并且 time_redeemed 不为 NULL 的注册 key 。这是我的查询，它正在获取结果，但使用相同的电子邮件/order_id 覆盖所有其他订单信息:

SELECT * FROM order_items
JOIN orders ON orders.order_id = order_items.order_id
JOIN reg_keys ON reg_keys.user_id=orders.user_id and reg_keys.product_id=order_items.product_id
WHERE count=1 and reg_keys.redeemed_by is not null
GROUP BY reg_key ORDER BY time_redeem DESC

这没有给我带来任何结果。我搞砸了什么？

Answer 1

不要使用分组依据并选择关键列

SELECT 
    reg_keys.reg_key * 
FROM 
    order_items
JOIN 
    orders ON orders.order_id = order_items.order_id
JOIN 
    reg_keys ON reg_keys.user_id = orders.user_id 
             AND reg_keys.product_id = order_items.product_id
WHERE 
    count = 1 
    AND reg_keys.redeemed_by IS NOT NULL
ORDER BY time_redeem DESC;