SQL 检查其他表中是否存在，如果不存在则检查它的类型 [英] SQL Checking if exists in other table, if not then check it's type

问题描述

我有这个查询，通过回调检查我是否在其他网站上投票，您可以在这里阅读更多相关信息

I have this query which checks if I have voted on other sites, by callbacks, you can read more about this here

SELECT sites.*, callback_votes.site_id AS voted
FROM sites
LEFT JOIN callback_votes
ON callback_votes.site_id = sites.id
AND callback_votes.ip = '127.0.0.1'
ORDER BY sites.id DESC

但现在我陷入了困境，如果类型等于none"，我需要将投票设置为 site_id.

But now I came to a dilemma, where I need to set voted as site_id if it's type equals to 'none'.

因此查询将查看 site.type 是否等于 'none'，然后将 site.id 设置为已投票，否则查看表 callback_votes 上是否有 id并将投票设置为 site_id，就像现在在查询中一样.

So the query will see if site.type equals to 'none', then set site.id as voted, else see if there are ids on the table callback_votes and set voted as site_id, like it does now in the query.

我该怎么做?如果我使用 WHERE 语句，它只会显示 sites.type = 'none' 的结果.

How can I do that? If I will use a WHERE statement, it will only show results with sites.type = 'none' only.

推荐答案

您在寻找 case 语句吗?

SELECT s.*, 
       (case when s.type = 'none' then s.id else cb.site_id end) as voted
FROM sites s LEFT JOIN
     callback_votes cb
     ON cb.site_id = s.id AND cb.ip = '127.0.0.1'
ORDER BY s.id DESC;

我发现逻辑的描述有点难以理解，因为cb.site_id = s.id.唯一的问题是该值何时为 NULL 或 s.id.

I find the description of the logic a bit hard to follow because cb.site_id = s.id. The only question is when the value will be NULL or s.id.

如果你想在这种情况下忽略投票，那么你也可以在 on 子句中包含条件:

If you want to ignore votes in this case, then you can include the condition in the on clause as well:

SELECT s.*, 
       (case when s.type = 'none' then s.id else cb.site_id end) as voted
FROM sites s LEFT JOIN
     callback_votes cb
     ON cb.site_id = s.id AND cb.ip = '127.0.0.1' AND s.type <> 'none'
ORDER BY s.id DESC;

请注意，将条件放在 on 子句中的第一个表上具有特殊的效果.当没有匹配项时，该行仍包含在输出中，只是没有其他表中的任何匹配列.

Note that putting conditions on the first table in an on clause has a peculiar effect. When there is no match, the row is still included in the output, just without any matching columns from the other table.

这篇关于SQL 检查其他表中是否存在，如果不存在则检查它的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！