SQL，用于忽略联接表中具有特定属性的行 [英] SQL for ignoring rows that have a particular property from a joined table

问题描述

更新: 感谢您的回答和评论.我决定采用其他方法，并提交了以下问题: 包含多个联接属性的SQL值，即使缺少属性类型也是如此

Update: thanks for the answers and comments. I decided to take a different approach and submitted this question: SQL to include multiple joined property values, even if property type is missing

我有两个桌子:饮料和物产.可以通过Drink_id加入它们.属性有几种可能的类型.我想创建一个查询，以查询饮料及其颜色属性，只要该饮料的类型没有甜味剂"即可.

I have two tables: drinks and properties. They can be joined by drink_id. Properties have several possible types. I want to create a query that reports drinks, with their color properties, as long as there is no property for that drink of type "sweetener."

Oracle 11，如果有所作为.

Oracle 11, if that makes a difference.

+----------+--------------+-------------+
| drink_id |  drink_name  | drink_brand |
+----------+--------------+-------------+
|        1 | orange juice | tropicana   |
|        2 | seltzer      | schweppes   |
|        3 | cola         | pepsi       |
|        4 | diet cola    | pepsi       |
+----------+--------------+-------------+

+----------+-----------+-----------+
| drink_id | prop_type | prop_val  |
+----------+-----------+-----------+
|        1 | color     | orange    |
|        2 | color     | clear     |
|        3 | color     | brown     |
|        4 | color     | brown     |
|        4 | sweetener | aspartame |
+----------+-----------+-----------+

所需的输出:

+--------------+-------------+-------------+
|  drink_name  | drink_brand | drink_color |
+--------------+-------------+-------------+
| orange juice | tropicana   | orange      |
| seltzer      | schweppes   | clear       |
| cola         | pepsi       | brown       |
+--------------+-------------+-------------+

我当时在想这样的事情，但是我不知道如何减肥的可乐，因为它具有甜味剂的特性.

I was thinking of something like this, but I don't know how to leave out diet cola, since it has a sweetener property.

select drink_name, drink_brand, colorprop.prop_val as drink_color 
from drinks
join properties colorprop
on drinks.drink_id = properties.drink_id
where colorprop.prop_type = 'color'

推荐答案

可能需要进行一些调整以适应联接，我通常选择老式的位置，因此我将其添加到了最后.警告:不存在的话，在真正大容量的情况下可能会有点慢.

May have to tweak a bit to accommodate the join, I usually go for old-school wheres so I've just added it onto the end. Warning: not exists can be a tad slow on really high volumes.

select drink_name, drink_brand, colorprop.prop_val as drink_color 
from drinks
join properties colorprop
on drinks.drink_id = properties.drink_id
where colorprop.prop_type = 'color'
/* skip if there a sweetener for that drink */
and not exists
(select 1
from properties s
where s.drink_id = drinks.drink_id
and s.prop_type = 'sweetener'
)

这篇关于SQL，用于忽略联接表中具有特定属性的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！