MySql Multiple Table query with left join

Question

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

You are using the alias b twice. Also, you are using old school join syntax. Try to avoid putting commas in the FROM clause. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 1, 2017 at 3:22
i edit my code already and change my left join alias to ba but the result is still the same.. isn't the comma separating the table?. — user7378642
– user7378642, Commented Feb 1, 2017 at 3:28
Why are you using single quotes around your column aliases? Use AS "name" or just AS name rather than AS 'name'. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 1, 2017 at 3:35
the result is still the same.. it still return an error Unknown column 'a.id' in 'on clause' — user7378642
– user7378642, Commented Feb 1, 2017 at 3:44

spencer7593 · Accepted Answer · 2017-02-01 04:17:24Z

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.

Collectives™ on Stack Overflow

MySql Multiple Table query with left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related