通过联结表选择具有一组特定项的行 [英] 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);

现在，我要做的是运行两种类型的查询:

1. 选择同时拥有鸭子和蛋糕的客户名称(仅应返回简").
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屋！