SQL - 基于引用表的XOR

Say for example, I have the following two tables:

比方说，我有以下两个表：

TableA { _id }
TableB { _id, TableA_id, OptionString }

Each row in TableA has one or more rows in TableB referencing it (using the TableA_id foreign key).

TableA中的每一行都有一个引用它的TableB中的一行（使用TableA_id外键）。

My query lists rows in TableA to display to the user.

我的查询列出了TableA中要显示给用户的行。

OptionString can be one of two values - let's say "Option1" and "Option2".

OptionString可以是两个值中的一个 - 比如说“Option1”和“Option2”。

Now I want the query to still list rows in TableA, but I want to provide the user with a filter to say "only display rows in TableA where the OptionString has either "Option1" OR "Option2" (ie. exclusive or).

现在我希望查询仍然列出TableA中的行，但我想为用户提供一个过滤器来说“只显示TableA中的行，其中OptionString具有”Option1“或”Option2“（即异或）。

I've tried using COUNT, but the problem is that a zero count displays no results (not '0' as I would want).

我尝试过使用COUNT，但问题是零计数没有显示结果（不是我想要的'0'）。

I'm sure I must be missing something simple with this.

我相信我一定会错过一些简单的东西。

Any help would be greatly appreciated.

任何帮助将不胜感激。

Cheers, Dan.

干杯，丹。

2 个解决方案

#1

Try this, if I understand your question correctly

如果我正确理解你的问题，试试这个

select a._id
from tableA a
join tableB b on a._id=b.tableA_id
where b.optionString in ('Option1','Option2')
group by a._id
having count(*)=1

Revised query based on new information

基于新信息修改查询

select a._id
from tableA a
join (select distinct _id,tablea_id,optionString from TableB) b 
        on a._id=b.tableA_id
where b.optionString in ('Option1','Option2')
group by a._id
having count(*)=1

What I am doing here is forcing distinct values from tableB

我在这里做的是强制tableB的不同值

#2

A naive approach (might be able to improve upon this...)

一种天真的方法（可能能够改善这一点......）

SELECT * 
FROM TableA
WHERE _id IN 
  (SELECT TableA_id
   FROM TableB
   WHERE OptionString IN ('Option1', 'Option2')
  )
AND _id NOT IN
  (SELECT T1.TableA_id
   FROM TableB T1
   JOIN TableB T2 ON T1.TableA_id = T2.TableA_id
   WHERE T1.optionString = 'Option1'
     AND T2.optionString = 'Option2'
  )

The idea is simply to select qualifying records where the Option1 OR the Option2 are found, and to exclude the records where both Option1 AND Option2 are found.

这个想法只是选择找到Option1或Option2的合格记录，并排除发现Option1和Option2的记录。

If we know that a given Optionx value can only be found once for a given item of TableA, a group by query may be applicable (see Sparky's answer).

如果我们知道给定的Optionx值只能为TableA的给定项找到一次，则查询组可能适用（请参阅Sparky的答案）。

#1