查询带或不带值的 props 列表

发布于 2024-08-30 14:40:57 字数 1151 浏览 6 评论 0原文

我正在尝试在三个关系表上进行 SELECT ,如下所示:

table_materials
-> material_id
- material_name

table_props
-> prop_id
- prop_name

table_materials_props
- row_id
-> material_id
-> prop_id
- prop_value

在我的页面上,我想得到这样的结果,但我的查询有一些问题:

material    prop A    prop B    prop C    prop D    prop E
wood        350       NULL      NULL      84        16
iron        NULL      17        NULL      NULL      201
copper      548       285       99        NULL      NULL

所以查询应该返回类似的东西:

material   prop_name     prop_value
wood       prop A        350
wood       prop B        NULL
wood       prop C        NULL
wood       prop D        84
wood       prop E        16
// and go on with others rows

我想使用类似的东西:

SELECT *
FROM table_materials AS m
INNER JOIN table_materials_props AS mp
ON m.material_id = mp.material_id
INNER JOIN table_materials_props AS p
ON mp.prop_id = p.prop_id
ORDER BY p.prop_name

问题是查询不返回 NULL 值,并且我需要所有 prop 顺序相同code>materials 无论 prop 值是否为 NULL

我希望这个例子很清楚!

I'm trying to make a SELECT on three relational tables like these ones:

table_materials
-> material_id
- material_name

table_props
-> prop_id
- prop_name

table_materials_props
- row_id
-> material_id
-> prop_id
- prop_value

On my page, I'd like to get a result like this one but i have some problem with the query:

material    prop A    prop B    prop C    prop D    prop E
wood        350       NULL      NULL      84        16
iron        NULL      17        NULL      NULL      201
copper      548       285       99        NULL      NULL

so the query should return something like:

material   prop_name     prop_value
wood       prop A        350
wood       prop B        NULL
wood       prop C        NULL
wood       prop D        84
wood       prop E        16
// and go on with others rows

i thought to use something like:

SELECT *
FROM table_materials AS m
INNER JOIN table_materials_props AS mp
ON m.material_id = mp.material_id
INNER JOIN table_materials_props AS p
ON mp.prop_id = p.prop_id
ORDER BY p.prop_name

the problem is the query doesn't return the NULL values, and I need the same prop order for all the materials regardless of prop values are NULL or not

I hope this example is clear!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

dawn曙光 2024-09-06 14:40:57

交叉联接(或带条件的内部联接)所有选项,然后将它们左联接到关联实体表。即使关联表中不存在组合,左连接也将保留道具/材质。

未经测试:

SELECT * 
FROM table_materials AS m
INNER JOIN table_props as p 
LEFT JOIN table_material_props AS mp 
ON p.prop_id = mp.prop_id 
AND 
m.material_id = mp.material_id;
ORDER BY p.prop_name

Cross-join (or inner join w/o a condition) all the options, then left join those to the associative entity table. The left join will preserve prop/materials even when the combinations don't exist in the associative table.

Untested:

SELECT * 
FROM table_materials AS m
INNER JOIN table_props as p 
LEFT JOIN table_material_props AS mp 
ON p.prop_id = mp.prop_id 
AND 
m.material_id = mp.material_id;
ORDER BY p.prop_name
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文