我有3个相互关联的表,我正在尝试正确查询数据,以便获得所需的一切。
这是我的3张表:
order_items
+------------+--------------+------+-----+
| 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 |
+-----------+-------------+---------+------------+
我希望获得所有的注册密钥,其中count/quanity等于1,time_referenced不为NULL。这是我的查询,它正在获取结果,但使用相同的电子邮件/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
这没有给我带来任何结果。我搞砸了什么?
不要使用分组依据并选择关键列
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;