组合两个选择查询
当我使用两个查询时,首先调用一个查询,在循环第一个查询的结果期间调用第二个查询。我想合并这两个查询,但到目前为止还无法做到。查询所提取的表是:
+--------------+ +--------------+ +--------------------------+
| table_1 | | table_2 | | table_3 |
+----+---------+ +----+---------+ +----+----------+----------+
| id | name | | id | name | | id | tbl1_id | tbl2_id |
+----+---------+ +----+---------+ +----+----------+----------+
| 1 | tbl1_1 | | 1 | tbl2_1 | | id | 1 | 1 |
| 2 | tbl1_2 | | 2 | tbl2_2 | | id | 3 | 2 |
| 3 | tbl1_3 | | 3 | tbl2_3 | | id | 3 | 3 |
| 4 | tbl1_4 | +----+---------+ +----+----------+----------+
+----+---------+
table_1
和 table_2
之间存在多对多关系,table_3
中。到目前为止我一直在使用分离查询。第一个查询返回 table_1
的所有内容,第二个查询返回通过 table_3 连接到
。但是,我想消除循环并减少发送到服务器的查询量。我尝试过使用 table_1
的 table_2
的值JOIN
:
SELECT table_1.id, table_1.name, table_2.id, table_2.name
FROM table_3
LEFT JOIN table_1 ON (table_3.tbl1_id = table_1.id)
LEFT JOIN table_1 ON (table_2.tbl2_id = table_2.id)
这几乎返回了我想要的值,只是它只返回 table_3
中的值,而忽略了 table_1< 中的一些值/代码>。我尝试过使用子查询:
SELECT table_1.id,
table_1.name,
(SELECT table_2.id FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_id,
(SELECT table_2.name FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_name
FROM table_1
这给出了ERROR 1242
。到目前为止,我还没能做任何事情。我正在寻找的结果与此类似。
+---------------+---------------+---------------+---------------+
|table_1.id |table_1.name |table_2.id |table_2.name |
+---------------+---------------+---------------+---------------+
| 1 | tbl1_1 | 1 | tbl2_1 |
| 2 | tbl1_2 | | |
| 3 | tbl1_3 | 2 | tbl2_2 |
| 3 | tbl1_3 | 3 | tbl2_3 |
| 4 | tbl1_4 | | |
+---------------+---------------+---------------+---------------+
另外,我希望能够对 table_1.name
和 table_2.name
上的结果进行排序。如果有人有建议,请告诉我。
A the moment I am using two queries one is called initially, and the second is called during a loop through the results of the first. I want to combine both queries, but have been unable to so far. The tables the queries are pulling from are:
+--------------+ +--------------+ +--------------------------+
| table_1 | | table_2 | | table_3 |
+----+---------+ +----+---------+ +----+----------+----------+
| id | name | | id | name | | id | tbl1_id | tbl2_id |
+----+---------+ +----+---------+ +----+----------+----------+
| 1 | tbl1_1 | | 1 | tbl2_1 | | id | 1 | 1 |
| 2 | tbl1_2 | | 2 | tbl2_2 | | id | 3 | 2 |
| 3 | tbl1_3 | | 3 | tbl2_3 | | id | 3 | 3 |
| 4 | tbl1_4 | +----+---------+ +----+----------+----------+
+----+---------+
There is a many to many relationship between table_1
and table_2
in table_3
. I have been using to separate queries so far. One query to return all the contents of table_1
and a second query to return the values of table_2
that are connected to table_1
through table_3
. However, I would like to do away with the loop and lessen the amount of queries being sent to the server. I have tried using a JOIN
:
SELECT table_1.id, table_1.name, table_2.id, table_2.name
FROM table_3
LEFT JOIN table_1 ON (table_3.tbl1_id = table_1.id)
LEFT JOIN table_1 ON (table_2.tbl2_id = table_2.id)
This returned pretty much want I wanted except it only returned the values that were in table_3
leaving out some of the values from table_1
. I have tried using subqueries:
SELECT table_1.id,
table_1.name,
(SELECT table_2.id FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_id,
(SELECT table_2.name FROM table_2, table_3 WHERE table_2.id = table_3.tbl2_id AND table_1.id = table_3.tbl1_id) AS tbl_2_name
FROM table_1
This gave an ERROR 1242
. So far, I have not been able get anything to work. The result I am looking for is similar to this.
+---------------+---------------+---------------+---------------+
|table_1.id |table_1.name |table_2.id |table_2.name |
+---------------+---------------+---------------+---------------+
| 1 | tbl1_1 | 1 | tbl2_1 |
| 2 | tbl1_2 | | |
| 3 | tbl1_3 | 2 | tbl2_2 |
| 3 | tbl1_3 | 3 | tbl2_3 |
| 4 | tbl1_4 | | |
+---------------+---------------+---------------+---------------+
Also, I would like to be able to order the results on both table_1.name
and table_2.name
. If anyone has a suggestion please let me know.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
要从 table_1 中获取在其他表中没有匹配项的行,您应该使用 OUTER JOIN 而不是 INNER JOIN:
结果:
To get the rows from table_1 that have no matches in the other tables you should use an OUTER JOIN instead of an INNER JOIN:
Result:
所以你只是想打印出所有相关的对?怎么样:
So you're just trying to print out all of the related pairs? How about:
将
LEFT JOIN
更改为RIGHT JOIN
以及两个联接在查询中出现的顺序解决了此问题。这是工作代码的副本;Changing the
LEFT JOIN
toRIGHT JOIN
and the order in which the two joins appear in the query solved this problem. Here is a copy of the working code;