MySQL - 在一个查询中访问所有关系数据
我有一个关系mysql数据库设置。我想在一个查询中从主行中提取一项的所有数据以及所有关系数据(即多个关联行)。除了一个查询中的多个 JOIN 语句之外,最简单/最好的方法是什么?
当前使用的表和查询如下。
关系数据
------------------------------------------------------------------------------------------------------------------------------
| value_id | value_site_id | value_field_set_id | value_field_setting_id | value_parent_id | value_parent_type | value_value |
------------------------------------------------------------------------------------------------------------------------------
主表
-----------------------------------------------------------------------
| item_id | item_site_id | item_country_id | item_category_id | etc etc
-----------------------------------------------------------------------
查询是
SELECT fs.field_variable_name, fs.field_type, fv.value_value, fv.value_parent_id
FROM T_field_values AS fv
INNER JOIN T_field_settings AS fs ON fv.value_field_setting_id=fs.field_id
WHERE fv.value_parent_type=:type &&
fv.value_parent_id=:id;
I have a relation mysql database setup. and I want to pull all the data for one item from the main row and all the relational data (ie multiple associated rows) in one query. What's the easiest/best way to do this aside from a multiple JOIN statement in one query?
The tables and query currently used are below.
Relational data
------------------------------------------------------------------------------------------------------------------------------
| value_id | value_site_id | value_field_set_id | value_field_setting_id | value_parent_id | value_parent_type | value_value |
------------------------------------------------------------------------------------------------------------------------------
Primary Table
-----------------------------------------------------------------------
| item_id | item_site_id | item_country_id | item_category_id | etc etc
-----------------------------------------------------------------------
And the Query is
SELECT fs.field_variable_name, fs.field_type, fv.value_value, fv.value_parent_id
FROM T_field_values AS fv
INNER JOIN T_field_settings AS fs ON fv.value_field_setting_id=fs.field_id
WHERE fv.value_parent_type=:type &&
fv.value_parent_id=:id;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
最好的方法是使用多个
JOIN
子句,每个关系一个。不要害怕
JOIN
。这是标准做法。另一种方法是为每个查询使用一个子查询,但查询优化器无论如何都会将它们转换为 JOIN。
JOIN
查询将更易于阅读。编辑
我明白了,您有某种形式的 EAV 表。您的情况有点复杂,并且没有完整的结构(缺少 T_field_settings)和一些示例数据,我不会尝试提供确切的查询,但请查看我之前的答案,了解如何使用自联接以从 EAV 表获取结果。它应该让你走上正确的轨道。
The best way is to use multiple
JOIN
clauses, one for each relationship.Don't fear the
JOIN
. It's standard practice.The other way is to use a sub query for each one, but the query optimizer will turn them into JOINs anyway.
The
JOIN
query will be easier to read.EDIT
I see, you have some form of EAV tables. Your case is a bit complex, and without the complete structure (T_field_settings is missing) and some sample data, I'm not going to attempt to provide the exact query, but take a look at my previous answer here, on how to use a self join to get results from a EAV table. It should get you going on the right track.