英文:
How to remove common element from sql union
问题
你可以尝试使用以下查询来排除13：
SELECT DISTINCT id
FROM (
SELECT issueid as id FROM links WHERE parentid NOT IN (13)
UNION
SELECT parentid as id FROM links WHERE issueid NOT IN (13)
) AS subquery
WHERE id != 13;
这将返回排除了13之后的实体ID列表，即 (14, 15, 21, 23)。
英文:
I've got the following table:
| issueid | parentid | type |
|---|---|---|
| 12 | 13 | a |
| 13 | 16 | b |
| 14 | 21 | c |
| 15 | 23 | d |
Both issueid and parentid's are id's of the same entity. And I want to get a list of entity ids that are not in either of the columns.
E.g. I want the result set of (14, 21, 15, 23) when the input is 13 (Because 13 is on the first 2 rows).
SELECT issueid from links where parentid not in (13); -- This gives 13, 14, 15
UNION
SELECT parentid from links where issueid not in (13); -- This gives 13, 21, 23
-- UNION gives 13, 14, 15, 21, 23
But I want to remove 13 from the above result set. How can I do that?
答案1
得分: 1
你可以联合两个反连接。例如：
select issueid from links a
left join links b on b.parentid = a.issueid where b.parentid is null
union
select parentid from links a
left join links b on b.issueid = a.parentid where b.issueid is null
英文:
You can union two anti-joins. For example:
select issueid from links a
left join links b on b.parentid = a.issueid where b.parentid is null
union
select parentid from links a
left join links b on b.issueid = a.parentid where b.issueid is null
答案2
得分: 0
根据 @The Impaler 的回答，我找到了解决方案：
select a.issueid from links a
full outer join links b on b.parentid = a.issueid where b.parentid is null
and a.parentid not in (13)
union
select a.parentid from links a
full outer join links b on b.issueid = a.parentid where b.issueid is null
and a.issueid not in (13)
英文:
Based on the answer by @The Impaler I found the solution as:
select a.issueid from links a
full outer join links b on b.parentid = a.issueid where b.parentid is null
and a.parentid not in (13)
union
select a.parentid from links a
full outer join links b on b.issueid = a.parentid where b.issueid is null
and a.issueid not in (13)
答案3
得分: 0
只需进行轻微调整即可修复它：
SELECT issueid FROM links WHERE parentid NOT IN (13) AND issueid NOT IN (13); -- 这将返回14, 15
UNION
SELECT parentid FROM links WHERE parentid NOT IN (13) AND issueid NOT IN (13); -- 这将返回21, 23
英文:
Just a minor tweak will fix it:
SELECT issueid FROM links WHERE parentid NOT IN (13) AND issueid NOT IN (13); -- This returns 14, 15
UNION
SELECT parentid FROM links parentid NOT IN (13) AND issueid NOT IN (13); -- This returns 21, 23
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论