通过联结表选择具有一组特定项的行 [英] Select rows that have a specific set of items associated with them through a junction table
本文介绍了通过联结表选择具有一组特定项的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我们具有以下架构:
Suppose we have the following schema:
CREATE TABLE customers(
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE items(
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE customers_items(
customerid INTEGER,
itemid INTEGER,
FOREIGN KEY(customerid) REFERENCES customers(id),
FOREIGN KEY(itemid) REFERENCES items(id)
);
现在我们插入一些示例数据:
Now we insert some example data:
INSERT INTO customers(name) VALUES ('John');
INSERT INTO customers(name) VALUES ('Jane');
INSERT INTO items(name) VALUES ('duck');
INSERT INTO items(name) VALUES ('cake');
让我们假设John和Jane的ID为1和2,而Duck和Cake的ID为1和2. 让我们给约翰一只鸭子,给简一个鸭子和一个蛋糕.
Let's assume that John and Jane have id's of 1 and 2 and duck and cake also have id's of 1 and 2. Let's give a duck to John and both a duck and a cake to Jane.
INSERT INTO customers_items(customerid, itemid) VALUES (1, 1);
INSERT INTO customers_items(customerid, itemid) VALUES (2, 1);
INSERT INTO customers_items(customerid, itemid) VALUES (2, 2);
现在,我要做的是运行两种类型的查询:
- 选择同时拥有鸭子和蛋糕的客户名称(仅应返回简").
- 选择有鸭子而没有蛋糕的顾客的名字(只能返回"John").
推荐答案
对于列出的两种查询,可以使用EXISTS子句.下面是使用existing子句的示例查询:
For the two type of queries listed, you could use the EXISTS clause. Below is an example query using the exists clause:
SELECT cust.name
from customers AS cust
WHERE EXISTS (
SELECT 1
FROM items
INNER JOIN customers_items ON items.id = customers_items.itemid
INNER JOIN customers on customers_items.customerid = cust.id
WHERE items.name = 'duck')
AND NOT EXISTS (
SELECT 1
FROM items
INNER JOIN customers_items ON items.id = customers_items.itemid
INNER JOIN customers on customers_items.customerid = cust.id
WHERE items.name = 'cake')
这是一个有效的示例: http://sqlfiddle.com/#!6/3d362/2
Here is a working example: http://sqlfiddle.com/#!6/3d362/2
这篇关于通过联结表选择具有一组特定项的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文