连接表上的多个WHERE子句(multiple WHERE clauses on a joined table)

我有一个简单的应用程序，跟踪食客和他们喜欢的口味和甜点。 records表只是用餐者的名字和ID， mid表跟踪甜点和口味（再次通过链接到另一个值表的ID）。

CREATE TABLE IF NOT EXISTS `records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `records` (`id`, `name`) VALUES (1, 'Jimmy Jones'), (2, 'William Henry'); CREATE TABLE IF NOT EXISTS `mid` ( `id` int(11) NOT NULL AUTO_INCREMENT, `diner` int(11) NOT NULL, `dessert` int(11) NOT NULL DEFAULT '0', `flavor` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; INSERT INTO `mid` (`id`, `diner`, `dessert`, `flavor`) VALUES (1, 1, 3, 0), (2, 1, 2, 0), (3, 1, 15, 0), (4, 1, 0, 1), (5, 2, 3, 0), (6, 2, 6, 0), (7, 2, 0, 4), (8, 1, 34, 0), (9, 2, 0, 4), (10, 2, 0, 22);

我应该对简单的查询感到有点难过 - 我想从records表中获取满足某些甜点或风味要求的所有ID：

SELECT a.id FROM records AS a JOIN mid AS b ON a.id = b.diner WHERE b.dessert IN (3,2,6) AND b.flavor IN (4,22)

即使存在与where子句匹配的记录，此查询也不返回任何行。我很确定我错过了JOIN明显内容，但我尝试过INNER，OUTER，LEFT和RIGHT但没有成功。

有人能让我走上正确的轨道并解释我所缺少的东西吗？

谢谢

I have a simple application that tracks diners and their favorite flavors and desserts. The records table is just the diner's name and ID, the mid table tracks the desserts and flavors (again by an ID linked to another table of values).

CREATE TABLE IF NOT EXISTS `records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `records` (`id`, `name`) VALUES (1, 'Jimmy Jones'), (2, 'William Henry'); CREATE TABLE IF NOT EXISTS `mid` ( `id` int(11) NOT NULL AUTO_INCREMENT, `diner` int(11) NOT NULL, `dessert` int(11) NOT NULL DEFAULT '0', `flavor` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; INSERT INTO `mid` (`id`, `diner`, `dessert`, `flavor`) VALUES (1, 1, 3, 0), (2, 1, 2, 0), (3, 1, 15, 0), (4, 1, 0, 1), (5, 2, 3, 0), (6, 2, 6, 0), (7, 2, 0, 4), (8, 1, 34, 0), (9, 2, 0, 4), (10, 2, 0, 22);

I'm a little stumped by what should be a simple query-- I want to get all IDs from the records table where certain dessert or flavor requirements are met:

SELECT a.id FROM records AS a JOIN mid AS b ON a.id = b.diner WHERE b.dessert IN (3,2,6) AND b.flavor IN (4,22)

This query returns no rows, even though there are records that match the where clauses. I am pretty sure I'm missing something obvious with the JOIN but I've tried INNER, OUTER, LEFT and RIGHT with no success.

Can someone put me on the right track and explain what I'm missing?

Thanks

最满意答案

您似乎希望食客拥有这些组合。这是一种方式：

select diner from records group by diner having max(b.dessert = 3) = 1 and max(b.dessert = 2) = 1 and max(b.dessert = 6) = 1 and max(b.flavor = 4) = 1 and max(b.flavor = 22) = 1

这回答了你的评论：

select diner from records group by diner having max(case when b.dessert in (2, 3, 6) then 1 esle 0 end) = 1 and max(case when b.dessert in (4, 22) then 1 else 0 end) = 1

如果您只是在查找符合条件的记录，请使用：

select r.*, d.name from records r join diner d on r.diner = d.id where b.dessert IN (3,2,6) AND b.flavor IN (4,22)

如果这是你想要的，你的查询中的连接条件是错误的（a.id应该是a.diner）。

You seem to want diners that have the combinations. Here is one way:

select diner from records group by diner having max(b.dessert = 3) = 1 and max(b.dessert = 2) = 1 and max(b.dessert = 6) = 1 and max(b.flavor = 4) = 1 and max(b.flavor = 22) = 1

This answers your comment:

select diner from records group by diner having max(case when b.dessert in (2, 3, 6) then 1 esle 0 end) = 1 and max(case when b.dessert in (4, 22) then 1 else 0 end) = 1

If you are just looking for the records in a that match the conditions, use:

select r.*, d.name from records r join diner d on r.diner = d.id where b.dessert IN (3,2,6) AND b.flavor IN (4,22)

If this is what you want, the join condition in your query is wrong (a.id should be a.diner).

更多推荐