SQL Join（全部来自一个表，一个来自另一个）

I am trying to retrieve all fields from one table on [condition1] and combine one field from another table in the results on [condition2], but my joins are not too hot yet:

我试图从[condition1]上的一个表中检索所有字段,并在[condition2]上的结果中合并另一个表中的一个字段,但我的连接还不是太热了:

SELECT * FROM table1 WHERE myField=123
INNER JOIN table2
SELECT myField2 FROM table2
ON (table1.condition = table2.condition AND table1.condition2 = table2.condition2)

Assistance is appreciated

协助表示赞赏

4 个解决方案

#1

Your syntax needs some adjusting.

您的语法需要一些调整。

SELECT t1.*, t2.myField2
    FROM table1 t1
        INNER JOIN table2 t2
            ON t1.condition = t2.condition
                AND t1.condition2 = t2.condition2
    WHERE t1.myField = 123;

#2

The correct syntax is

正确的语法是

SELECT table1.*, table2.field1
FROM table1
INNER JOIN table2
ON table1.condition = table2.condition 
   AND table1.condition2 = table2.condition2
WHERE myField=123

#3

SELECT t1.*, t2.MyField2
FROM table1 t1
INNER JOIN table2
   ON t1.condition = t2.condition 
  AND t1.condition2 = t2.condition2
WHERE t1.myField=123

Problems.

All the columns you want to display in the result from MUST be listed in the outer most select.

您希望在结果中显示的所有列必须列在最外面的选择中。

You don't need multiple SELECT statements unless you're executing a union or a sub query

除非您正在执行联合或子查询,否则不需要多个SELECT语句

You can only use a select within a sub query if you wrap it in () and alias it as. Select field from table A inner join (Select * from table2) B on A.ID = B.ID

如果将其包装在()中并且将其别名为,则只能在子查询中使用select。从表A中选择字段A内部联接(从表2中选择*)B在A.ID = B.ID上

Where clause belongs after the FROM and joins and join criteria.

Where子句属于FROM和join和join条件之后。

In General... SQL syntax order is:

一般来说...... SQL语法顺序是:

SELECT
FROM
JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY

#4

Alternatively, you can put both the join conditions in the where clause and join to the second table like this:

或者,您可以将连接条件放在where子句中并连接到第二个表,如下所示:

SELECT t1.*
      ,t2.myField2
FROM table1 t1
    ,table2 t2
WHERE t1.condition = t2.condition
    AND t1.condition2 = t2.condition2
    AND myField = 123

#1