MySQL 如何使用 where 子句编写左连接来限制跨多个表的左侧部分？

How do a write a left join with a where clause restricting the left-hand portion across multiple tables?

提问by RonLugge

I'm trying to write an SQL query that will gather certain data in one table, based on data across several other tables. That part was easy enough. Unfortunately, I also need to recover data from a second table, and that needs to be done with a left join.

我正在尝试编写一个 SQL 查询，该查询将根据其他几个表中的数据在一个表中收集某些数据。那部分很容易。不幸的是，我还需要从第二个表中恢复数据，这需要通过左连接来完成。

If I just do a left join, e. g.

如果我只是做一个左连接，例如

select *
from table_A A
left join table_B B on A.id=B.id

everything is fine. I can add in a where clause safely enough, like

一切安好。我可以足够安全地添加 where 子句，例如

where A.id>5

The issue occurs when I try to have more than one table on the left hand side, for example

例如，当我尝试在左侧放置多个桌子时会出现问题

select *
from table_A, table_C
left join table_B on A.id=B.id
where table_A.id=table_C.id

All of a sudden, A.id is no longer a recognized column. I tried changing it so that I have select A.id as foo, and then do on foo=B.id, but foo isn't recognized either. ( Unknown column 'bla' in 'on clause')

突然之间，A.id 不再是公认的列。我尝试更改它，以便我选择 A.id 作为 foo，然后在 foo=B.id 上执行，但 foo 也无法识别。（“on 子句”中的未知列“bla”）

While there are many references out there than explain how to do a join clause, and some examples even use a simple where clause, I can't find any that combine a join clause, a where clause, and multiple tables on the left hand side.

虽然有很多参考资料没有解释如何执行 join 子句，有些示例甚至使用了一个简单的 where 子句，但我找不到任何将 join 子句、where 子句和左侧多个表组合在一起的内容.

For background on the question:

关于这个问题的背景：

My original query was:

我原来的查询是：

select SQL_CALC_FOUND_ROWS tutor_user.username
    , tutor_user.userid
    , tutor_tutor.rate
    , tutor_user.username 
from tutor_user
    , tutor_attends
    , tutor_tutors_in
    , tutor_subject
    , tutor_tutor 
where tutor_user.userid=tutor_attends.userid 
    and tutor_attends.schoolid=80 
    and tutor_tutors_in.userid=tutor_user.userid 
    and tutor_tutors_in.subjectid=tutor_subject.subjectID 
    and tutor_subject.name='Aerospace Studies' 
    and tutor_tutor.userid=tutor_user.userid //ERROR
LIMIT 0, 15

Unfortunately, there's no guarantee that tutor_tutor information will exist for every user -- a minor flaw that's persisting from poor decisions early on in the website/database design cycle. So, the easy solution is to turn it into a join.

不幸的是，不能保证每个用户都存在tutor_tutor 信息——这是一个小缺陷，由于网站/数据库设计周期早期的错误决策而持续存在。因此，简单的解决方案是将其转换为连接。

The problem is, I can't find any good tutorials explaining how to combine a join with where clauses. My best effort comes up with the following:

问题是，我找不到任何好的教程来解释如何将连接与 where 子句结合起来。我最大的努力想出了以下几点：

select SQL_CALC_FOUND_ROWS tutor_user.username
    , tutor_user.userid
    , tutor_tutor.rate
    , tutor_user.username 
from tutor_user
    , tutor_attends
    , tutor_tutors_in
    , tutor_subject
LEFT JOIN tutor_tutor
    on tutor_user.userid=tutor_tutor.userid
where tutor_user.userid=tutor_attends.userid 
    and tutor_attends.schoolid=80 
    and tutor_tutors_in.userid=tutor_user.userid 
    and tutor_tutors_in.subjectid=tutor_subject.subjectID 
    and tutor_subject.name='Aerospace Studies' 
    LIMIT 0, 15

回答by Marc B

Ideally, you shouldn't mix the lazy join syntax (select ... from a,b,c) with the more specific select ... from a join B ... join c .... And doing a select *can be problematic as well, especially you're not going to be using all of the fields, or need to refer to the fields by name instead of position.

理想情况下，您不应将惰性连接语法 ( select ... from a,b,c) 与更具体的select ... from a join B ... join c .... 并且执行 aselect *也可能有问题，尤其是您不会使用所有字段，或者需要按名称而不是位置来引用字段。

So your query should be:

所以你的查询应该是：

SELECT A.field, A.otherfield, B.field, B.otherfield, C.yetanotherfield
FROM table_A AS A
JOIN table_B as B on A.id = B.a_id
JOIN table_C as C on B.id = C.b_id
WHERE ...

回答by Code Magician

You can put multiple clauses in the JOINe.g.

您可以在JOINeg 中放置多个子句

SELECT * 
FROM table_a A
    LEFT OUTER JOIN table_b B
        ON A.col1 = B.col1 
             AND A.col2 = B.col2
             AND B.col3 > 5

You can write the criteria in the where clause, but that is outdated notation. If you do, however, want to pu the join clause in the WHEREanyway then add an ORto handle NULLotherwise it ceases to be a LEFT JOIN

您可以在 where 子句中编写条件，但这是过时的表示法。但是，如果您确实想要将 join 子句放入WHERE无论如何然后添加一个OR来处理，NULL否则它就不再是一个LEFT JOIN

e.g.

例如

SELECT * 
FROM table_a A
    LEFT OUTER JOIN table_b B
        ON A.col1 = B.col1 
             AND A.col2 = B.col2
WHERE (B.col3 > 5 OR B.col3 IS NULL)

回答by RicardoS

ok. A lot of stuff...

好的。很多东西...

select * from table_A, table_C left join table_B on A.id=B.id where table_A.id=table_C.id All of a sudden, A.id is no longer a recognized column. I tried changing it so that I have select A.id as foo, and then do on foo=B.id, but foo isn't recognized either. ( Unknown column 'bla' in 'on clause')

select * from table_A, table_C left join table_B on A.id=B.id where table_A.id=table_C.id 突然之间，A.id 不再是可识别的列。我尝试更改它，以便我选择 A.id 作为 foo，然后在 foo=B.id 上执行，但 foo 也无法识别。（“on 子句”中的未知列“bla”）

This problem of collumn not recognized there is no table or alias named A. You need to put the alias in front of table's name:

这个collumn not识别的问题，没有表名或别名为A，需要在表名前加上别名：

select *
from table_A A, table_C
left join table_B on A.id=B.id
where table_A.id=table_C.id

But why are you using join with a comma (,)?
It's better use join statement. It should be something like this:

但是为什么要使用带逗号 (,) 的连接？
最好使用 join 语句。它应该是这样的：

select * /* any collmn you want */
from tutor_user u
  left join tutor_attends a ON a.userid = u.userid
  left join tutor_tutors_in t ON t.userid = u.userid  
  left join tutor_subject s ON s.subjectid = t.subjectid
  LEFT JOIN tutor_tutor tt on u.userid = tt.userid 

where tutor_attends.schoolid=80 
and tutor_subject.name='Aerospace Studies'

Take a look on 'alias'.
Also you can try the doc: http://dev.mysql.com/doc/refman/5.0/en/select.html

看看“别名”。
您也可以尝试文档：http: //dev.mysql.com/doc/refman/5.0/en/select.html

回答by dryliketoast

Probably need something like this:

可能需要这样的东西：

select A.*, B.field1, C.field3 from A
    left join B on A.id=B.id
    left join C on A.id=C.id
    where C.field5 = 'foo'