具有左连接的MySql多表查询(MySql Multiple Table query with left join)

具有左连接的MySql多表查询(MySql Multiple Table query with left join)

我问这个，因为我找不到使用左连接查询多个表的确切示例，我是一个新的使用连接。 我在这里要做的是加入表user_list和leave_record ，以便从user_list表的两个表中获取数据。 因为没有这样做，一些没有离开记录的用户将不会被显示。

现在，当我运行以下代码时，它Unknown column 'a.id' in 'on clause' 。 但是这个专栏确实存在于我的表格中。 如果有人能指出我的错误，我很感激。 先谢谢你。 :-)

SELECT a.id, CONCAT(a.firstname, a.lastname) AS "name", a.date_joined,a.job_status, d.description, d.annual_cf AS 'allowed_cf', f.2016 AS "lastyear", g.2016 AS "last_annual_cf", h.2016 AS "ot_convert", f.2017 AS "current_ent", g.2017 AS "current_annual_cf", h.2017 AS "current_ot_convert", SUM(CASE WHEN b.start_date BETWEEN c.entitlement_start_date AND c.entitlement_extention_date AND b.submit_date BETWEEN c.entitlement_start_date AND c.system_freez_start_date AND b.leave_type = '1' AND b.s_status = 'Approved' AND b.status <> 'Canceled' THEN b.no_days ELSE 0 END) a_annual FROM user_list a, leave_records b, system_settings c, job_category d, annual_leave_ent f, annual_cf g, ot_convert h LEFT JOIN (SELECT id FROM leave_records) AS ba ON a.id = ba.id WHERE f.id = a.id AND a.enabled <> 'no' AND g.id = a.id AND h.id = a.id AND d.id = a.job_category AND a.company='14' AND c.year_id='2016' GROUP BY a.id ORDER BY a.id

Im asking this since I couldn't find the exact example of querying multiple table with left join and I am kind of new to using join. What I'm trying to do here is to join Table user_list and leave_record in order to fetch data from both table base on user_list table. Because without doing so, some user that has no leave record yet will not be shown.

Now when I run the following code, it says Unknown column 'a.id' in 'on clause'. But the column did exist in my table. I do appreciate it if someone can point me my mistake. Thank you in advance. :-)

SELECT a.id, CONCAT(a.firstname, a.lastname) AS "name", a.date_joined,a.job_status, d.description, d.annual_cf AS 'allowed_cf', f.2016 AS "lastyear", g.2016 AS "last_annual_cf", h.2016 AS "ot_convert", f.2017 AS "current_ent", g.2017 AS "current_annual_cf", h.2017 AS "current_ot_convert", SUM(CASE WHEN b.start_date BETWEEN c.entitlement_start_date AND c.entitlement_extention_date AND b.submit_date BETWEEN c.entitlement_start_date AND c.system_freez_start_date AND b.leave_type = '1' AND b.s_status = 'Approved' AND b.status <> 'Canceled' THEN b.no_days ELSE 0 END) a_annual FROM user_list a, leave_records b, system_settings c, job_category d, annual_leave_ent f, annual_cf g, ot_convert h LEFT JOIN (SELECT id FROM leave_records) AS ba ON a.id = ba.id WHERE f.id = a.id AND a.enabled <> 'no' AND g.id = a.id AND h.id = a.id AND d.id = a.job_category AND a.company='14' AND c.year_id='2016' GROUP BY a.id ORDER BY a.id

最满意答案

不要将连接操作的旧学校逗号语法与较新的JOIN关键字混合使用。 并将连接谓词移动到适当的ON子句而不是WHERE子句。

（它已经超过了抛弃逗号语法的时间; JOIN关键字足够长，以至于很难将其称为“更新”。对于任何新查询，请丢弃逗号。（是的，逗号语法是仍然有效，但这是为了兼容性，所以古老的SQL仍将运行。）

我不明白为什么你需要一个交叉连接和一个外部连接到leave_records表。 但目前尚不清楚你想要实现的目标。

我们不知道引用表中的id是否为UNIQUE。 如果不了解这一点，看起来有可能生产半笛卡尔积。

由于没有样本数据，也没有预期的结果可供比较，我们只是猜测。

但就LEFT OUTER JOIN的查询语法而言，我认为查询看起来像这样。 （请注意，删除了对leave_records的第一个引用。）

SELECT a.id , CONCAT(a.firstname,a.lastname) AS `name` , a.date_joined , a.job_status , d.description , d.annual_cf AS `allowed_cf` , f.2016 AS `lastyear` , g.2016 AS `last_annual_cf` , h.2016 AS `ot_convert` , f.2017 AS `current_ent` , g.2017 AS `current_annual_cf` , h.2017 AS `current_ot_convert` , SUM( CASE WHEN b.start_date BETWEEN c.entitlement_start_date AND c.entitlement_extention_date AND b.submit_date BETWEEN c.entitlement_start_date AND c.system_freez_start_date AND b.leave_type = '1' AND b.s_status = 'Approved' AND b.status <> 'Canceled' THEN b.no_days ELSE 0 END ) AS a_annual FROM user_list a JOIN system_settings c ON c.year_id = '2016' JOIN job_category d ON d.id = a.job_category JOIN annual_leave_ent f ON f.id = a.id JOIN annual_cf g ON g.id = a.id JOIN ot_convert h ON h.id = a.id LEFT JOIN leave_record b ON b.id = a.id WHERE a.enabled <> 'no' AND a.company = '14' GROUP BY a.id ORDER BY a.id

我将在没有 GROUP BY且没有 SUM()聚合的情况下测试查询，以验证查询是否返回了我们期望的行。 在我确定之后，我会添加GROUP BY和SUM()聚合以获得合并结果。

Don't mix the old school comma syntax for the join operation with the newer JOIN keyword. And move the join predicates to an appropriate ON clause instead of the WHERE clause.

(It's well past the time to ditch the comma syntax; the JOIN keyword has been around sufficiently long enough that it's difficult to refer to it as being "newer". For any new queries, ditch the comma. (Yes, the comma syntax is still valid, but that's for compatibility, so ancient SQL will still run.)

I'm not understanding why you need both a cross join and and an outer join to the leave_records table. But it's not clear what you are attempting to achieve.

We don't know if id is UNIQUE in the referenced tables. Without knowledge of that, it looks like there's potential to produce a semi-Cartesian product.

With no sample data, and no expected result to compare to, we're just kind of guessing.

But in terms of the query syntax for a LEFT OUTER JOIN, I think the query would look something like this. (Note that the first reference to leave_records is removed.)

SELECT a.id , CONCAT(a.firstname,a.lastname) AS `name` , a.date_joined , a.job_status , d.description , d.annual_cf AS `allowed_cf` , f.2016 AS `lastyear` , g.2016 AS `last_annual_cf` , h.2016 AS `ot_convert` , f.2017 AS `current_ent` , g.2017 AS `current_annual_cf` , h.2017 AS `current_ot_convert` , SUM( CASE WHEN b.start_date BETWEEN c.entitlement_start_date AND c.entitlement_extention_date AND b.submit_date BETWEEN c.entitlement_start_date AND c.system_freez_start_date AND b.leave_type = '1' AND b.s_status = 'Approved' AND b.status <> 'Canceled' THEN b.no_days ELSE 0 END ) AS a_annual FROM user_list a JOIN system_settings c ON c.year_id = '2016' JOIN job_category d ON d.id = a.job_category JOIN annual_leave_ent f ON f.id = a.id JOIN annual_cf g ON g.id = a.id JOIN ot_convert h ON h.id = a.id LEFT JOIN leave_record b ON b.id = a.id WHERE a.enabled <> 'no' AND a.company = '14' GROUP BY a.id ORDER BY a.id

I would test the query without the GROUP BY and without the SUM() aggregate, in order to verify that the query is returning the rows we expect. After I was sure of that, then I would add in the GROUP BY and SUM() aggregate to get the consolidated results.