mysql 仅当另一行的字段等于时才选择一行

发布于 2024-09-16 13:48:34 字数 387 浏览 3 评论 0原文

我有这两个表：

tAccounts
id、名称、server_id

tFriends
id_lo, id_hi

现在，我正在尝试这个查询：

SELECT FR.id_lo AS id_friend
FROM tFriends FR, tAccounts AC
WHERE (FR.id_hi = 4 && AC.server_id != -1)

........ 为了获取某个用户的好友，同时确保他的好友的 server_id 不同于“-1” ！

有什么想法吗？ WHERE 子句中的 'AC.server_id != -1' 约束似乎没有给我预期的结果。

原文

I have these two tables:

tAccounts
id, name, server_id

tFriends
id_lo, id_hi

Now, I'm trying this query:

SELECT FR.id_lo AS id_friend
FROM tFriends FR, tAccounts AC
WHERE (FR.id_hi = 4 && AC.server_id != -1)

........ In order get the friends of a certain user, while making sure his friend's server_id is different than '-1' !

Any idea? It seems like the 'AC.server_id != -1' constraint in the WHERE clause doesn't give me the expected results.

分享到QQ

分享到微博

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

发布评论

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

小ぇ时光︴ 2024-09-23 13:48:34

你的加盟条件在哪里？应该是这样的：（我假设tFriends.id_lo是朋友的帐户id）

SELECT FR.id_lo AS id_friend
FROM tFriends FR 
INNER JOIN tAccounts AC ON (AC.id = FR.id_lo AND AC.server_id != -1)
WHERE FR.id_hi = 4 ;

Where is you join condition ? It should be something like this: (I assume that tFriends.id_lo is an account id of friend)

SELECT FR.id_lo AS id_friend
FROM tFriends FR 
INNER JOIN tAccounts AC ON (AC.id = FR.id_lo AND AC.server_id != -1)
WHERE FR.id_hi = 4 ;

回复收藏 0 原文

假装不在乎 2024-09-23 13:48:34

您需要连接两个表；否则，您将得到笛卡尔积（将 tFriends 中 id_hi = 4 的所有行与 tAccounts 中 server_id 不为 -1 的所有行组合起来）。

试试这个：

SELECT FR.id_lo AS id_friend
FROM tFriends FR
INNER JOIN tAccounts AC ON AC.id = FR.id_hi
WHERE (FR.id_hi = 4 && AC.server_id != -1)

You need to join the two tables; otherwise, you get a Cartesian product (combining all rows of tFriends where id_hi = 4 with all rows of tAccounts where server_id is not -1).

Try this:

SELECT FR.id_lo AS id_friend
FROM tFriends FR
INNER JOIN tAccounts AC ON AC.id = FR.id_hi
WHERE (FR.id_hi = 4 && AC.server_id != -1)

回复收藏 0 原文

七堇年 2024-09-23 13:48:34

尝试连接帐户表两次：

SELECT *
FROM tAccounts a1
JOIN tFriends f ON a1.id = f.id_lo
JOIN tAccounts a2 ON a2.id = f.id_hi
WHERE a1.id = 4 AND a2.server_id != -1
   OR a2.id = 4 AND a1.server_id != -1

或使用 UNION：

SELECT *
FROM tAccounts a
JOIN tFriends f ON a.id = f.id_lo
WHERE f.id_hi = 4 AND a.server_id != -1
UNION
SELECT *
FROM tAccounts a
JOIN tFriends f ON a.id = f.id_hi
WHERE f.id_lo = 4 AND a.server_id != -1

Try joining the account table twice:

SELECT *
FROM tAccounts a1
JOIN tFriends f ON a1.id = f.id_lo
JOIN tAccounts a2 ON a2.id = f.id_hi
WHERE a1.id = 4 AND a2.server_id != -1
   OR a2.id = 4 AND a1.server_id != -1

Or use a UNION:

SELECT *
FROM tAccounts a
JOIN tFriends f ON a.id = f.id_lo
WHERE f.id_hi = 4 AND a.server_id != -1
UNION
SELECT *
FROM tAccounts a
JOIN tFriends f ON a.id = f.id_hi
WHERE f.id_lo = 4 AND a.server_id != -1

回复收藏 0 原文

~没有更多了~