我有3个表,希望进行sql查询以正确查看它们。
1.)Zakaz
id | Comment
2.)托瓦尔
id | id_clients | zakaz
3.)用户
id | name
它们连接为:
zakaz.id = tovar.zakaz
tovar.id_clients = users.id
我想看
zakaz.id | users.name
但我想限制每个zakaz只能有一个字符串,所以我做了:
SELECT zakaz.id, zakaz.comment, tovar.id_clients
FROM zakaz
JOIN tovar ON zakaz.id = tovar.zakaz
GROUP BY zakaz.id
然后我想加入用户,但它不起作用
SELECT zakaz.id, zakaz.comment, tovar.id_clients, users.name
FROM zakaz
JOIN tovar ON zakaz.id = tovar.zakaz
GROUP BY zakaz.id
JOIN users ON users.id = tovar.id_clients
它将在Zakaz
上显示最新id
SELECT a.name, c.id
FROM Users a
INNER JOIN
(
SELECT id_clients, zakaz, MAX(id) maxID
FROM Tovar
GROUP BY id_clients, zakaz
) d
ON d.id_clients = a.id
INNER JOIN Zakaz c
ON d.zakaz = c.id