Reputation: 3
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
Upvotes: 0
Views: 1781
Reputation: 108530
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.
Upvotes: 2