SQL包含多个联接的属性值，即使缺少属性类型 [英] SQL to include multiple joined property values, even if property type is missing
问题描述
This question is based on " SQL for ignoring rows that have a particular property from a joined table ." I got helpful answers and comments, but I've decided to go in a different direction from my original request.
我有两个桌子:饮料和物产.可以通过Drink_id加入它们.属性有几种可能的类型.我想创建一个报告饮料的查询，其中每个饮料的属性都有一个列，即使该列为null.如果同一饮料有多次出现相同属性类型的情况，我想我想要所有笛卡尔组合，但这不是
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 a column for each of their properties, even if it is null. If there were multiple occurrences of the same attribute type for the same drink, I guess I would want all Cartesian combinations, but that's not
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 | sweetener | aspartame |
+----------+-----------+-----------+
所需的输出:
+--------------+-------------+-------------+-----------+
| drink_name | drink_brand | drink_color | sweetener |
+--------------+-------------+-------------+-----------+
| orange juice | tropicana | orange | <null> |
| seltzer | schweppes | clear | <null> |
| cola | pepsi | brown | <null> |
| diet cola | pepsi | <null> | aspartame |
+--------------+-------------+-------------+-----------+
我当时正在考虑类似的事情，但是如果给定饮料中的一种或两种属性均不可用，我不知道如何添加行.
I was thinking of something like this, but I don't know how to include rows if one or both properties are not available for a given drink.
select drink_name, drink_brand, colorprop.prop_val as drink_color
from drinks
left join properties colorprop
on drinks.drink_id = colorprop.drink_id
where colorprop.prop_type = 'color'
left join properties sweetprop
on drinks.drink_id = sweetprop.drink_id
where sweetprop.prop_type = 'sweetener'
更新: 如评论和答案所示，我的问题主要是草率的语法错误.我可以使用以下R代码对此进行测试:
Update: As indicated in the comment and answer, my problems were mostly hasty syntax errors. I could have tested this with the following R code:
library(sqldf)
drinks <- data.frame(drink_id = c(1,2,3,4),
drink_name = c("orangejuice", "seltzer", "cola", "dietcola"),
drink_brand = c("tropicana", "schweppes", "pepsi", "pepsi"))
names(drinks) <- c("drink_id", "drink_name", "drink_brand")
properties <- data.frame(drink_id = c(1,2,3,4),
prop_type = c("color", "color", "color", "sweetener"),
prop_val = c("orange", "clear", "brown", "aspartame"))
names(properties) <- c("drink_id", "prop_type", "prop_val")
drinkquery <-
"select drink_name, drink_brand,
colorprop.prop_val as drink_color, sweetprop.prop_val as sweetener
from drinks
left join properties colorprop
on drinks.drink_id = colorprop.drink_id AND colorprop.prop_type = 'color'
left join properties sweetprop
on drinks.drink_id = sweetprop.drink_id AND sweetprop.prop_type = 'sweetener'"
sqldf(drinkquery)
推荐答案
我从未像您一样在JOIN上看到WHERE子句，但您却很亲密.我认为，将prop_type部分作为JOIN条件的一部分应该可以为您提供所需的东西.
I've never seen WHERE clauses on the JOINs like you have, but you were close. Making the prop_type part of the JOIN condition should get you what you are looking for, I think.
select drink_name, drink_brand, colorprop.prop_val as drink_color,
sweetprop.prop_val as sweetener
from drinks
left join properties colorprop
on drinks.drink_id = colorprop.drink_id AND colorprop.prop_type = 'color'
left join properties sweetprop
on drinks.drink_id = sweetprop.drink_id AND sweetprop.prop_type = 'sweetener'
这篇关于SQL包含多个联接的属性值，即使缺少属性类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
