JOIN 字段不等于值的表

发布于 2024-12-16 23:12:43 字数 580 浏览 0 评论 0原文

我有这个查询：

SELECT `A`.*, `LNK`.`act_id`, `LNK`.`remaining`, `U`.`username` as U_username
FROM (`anagrafiche` as A)
LEFT JOIN `lnk_ana-act` AS LNK ON `A`.`id` = `LNK`.`ana_id`
LEFT JOIN `users` AS U ON `A`.`uid` = `U`.`id`
WHERE (`LNK`.`act_id` != 57 OR `LNK`.`act_id` IS NULL) AND A.closed = '0'
LIMIT 10

此查询选择 act_id 不为 57 的所有字段，导致第一个表“anagrafiche”有多个重复项。这样做显然是错误的，这种情况我该怎么办？

感谢

编辑澄清。

无论有多少 lnk_ana-act 记录，我只想要第一个表的一条记录。更准确地说，lnk_ana-act 表中有数千条记录不等于 act_id != 57，但我只需要“anagrafiche”表的一条唯一记录，无论该表中有多少条记录连接表

原文

I have this query:

SELECT `A`.*, `LNK`.`act_id`, `LNK`.`remaining`, `U`.`username` as U_username
FROM (`anagrafiche` as A)
LEFT JOIN `lnk_ana-act` AS LNK ON `A`.`id` = `LNK`.`ana_id`
LEFT JOIN `users` AS U ON `A`.`uid` = `U`.`id`
WHERE (`LNK`.`act_id` != 57 OR `LNK`.`act_id` IS NULL) AND A.closed = '0'
LIMIT 10

This query selects ALL fields where act_id is not 57, resulting on several duplicates of the first table "anagrafiche". It's obviously wrong to do it like this, what should I do in this case?

Thanks

EDIT CLARIFICATION.

I want only one record of the first table regardless of how many lnk_ana-act records there are. To be more precise there are thousands of records lnk_ana-act table that are not equal to act_id != 57, but I only need one unique record of the "anagrafiche" table regardless of how many records in the joined table

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愚人国度 2024-12-23 23:12:43

如果我明白你需要什么。试试这个：

SELECT A.*, LNK.act_id, LNK.remaining, U.username as U_username
FROM anagrafiche A LEFT JOIN lnk_ana-act LNK 
    ON A.id = LNK.ana_id
LEFT JOIN users U 
    ON A.uid = U.id
WHERE LNK.act_id <> 57 AND A.closed = '0'
GROUP BY A.id
LIMIT 10

If I understand what you need. try this:

SELECT A.*, LNK.act_id, LNK.remaining, U.username as U_username
FROM anagrafiche A LEFT JOIN lnk_ana-act LNK 
    ON A.id = LNK.ana_id
LEFT JOIN users U 
    ON A.uid = U.id
WHERE LNK.act_id <> 57 AND A.closed = '0'
GROUP BY A.id
LIMIT 10

回复收藏 0 原文

~没有更多了~