如何使用一个查询查询相关的三个相关表格
问题描述:
我有三个下面的表格。关系是,每个report_param和report_frequency使用中间表report_freq_map连在一起。是否可以构造一个SQL,以便用户可以使用频率='每日'等约束来选择所有的report_param行,而不是使用频率='1'。如何使用一个查询查询相关的三个相关表格
感谢, -Peter
mysql> describe report_params;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| test_level | varchar(45) | NO | | NULL | |
| sequence_name | varchar(45) | NO | | NULL | |
| step_name | varchar(45) | NO | | NULL | |
| descriptions | text | NO | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
mysql> describe report_frequency;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| frequency | varchar(25) | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
mysql> describe report_freq_map;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| report_id | int(11) | NO | PRI | 0 | |
| freq_id | int(11) | NO | PRI | 0 | |
+-----------+---------+------+-----+---------+-------+
答
尝试连接表使用JOIN
:
SELECT report_params.*
FROM report_params
JOIN report_freq_map ON report_id = report_params.id
JOIN report_frequency ON freq_id = report_frequency.id
WHERE frequency = 'daily'
答
SELECT report_params.id, test_level, sequence_name, step_name, descriptions
FROM report_params
JOIN report_freq_map
ON report_params.id = report_id
JOIN report_frequency
ON freq_id = report_frequency.id
WHERE frequency = 'daily'
(SELECT之后添加DISTINCT,如果你想允许多个frequncy,或者你可能有重复的条目)
一个表中的两个主键?以前没见过。 – John 2010-08-26 20:31:41
复合主键 – 2010-08-26 20:34:44
@John at CashCommons:你读错了。这不是两个主键,而是一个由两列组成的主键。 – Jasper 2010-08-26 20:34:54