MySQL Forums
Forum List  »  Newbie

Re: MYSQL monthly record view
Posted by: Phillip Ward
Date: June 20, 2023 02:02AM

Your join needs to go the "other way around".

Remember what the LEFT join does - all the rows in the "left" table, plus any rows that happen to match in the "right" table. Since you're "driving" this join from attTable, you'll get the rows in that table, plus any that match in the calendar (which should be all of them).

Swap the tables round, starting from the calendar table and LEFT JOINing to attTable,

SELECT a, b, c, d 
FROM mycalendar 
LEFT JOIN attTable
       ON date( mycalendar.myday ) = date( attTable.TimeIN ) 
      AND attTable.EMPID = '100003'

Just change the LEFT join in your original to a RIGHT join, which has exactly the same nett effect as the above.
IMHO, though, it doesn't read quite so naturally as the LEFT join variant and, as we all know, we'll spend far more time reading SQL (and code) than we will writing it!

Note that I've moved the condition on EMPID out of the WHERE and into the LEFT JOIN. That's because a LET JOIN with a condition on the "right" table collapses the LEFT JOIN into a regular INNER JOIN. Making the condition as part of the join itself avoids this nasty little "Gotcha".

Also, seriously consider changing attTable.IimeIN and calendar.myday to use proper, Date Data Types. Casting character values into Dates on the fly is both error prone and slows things down, both with the overhead of the conversion and preventing the database from using Indexes (because it's having to do a calculation as part of the query).

And, of course, don't use `select *` in Application code or SQL.

Regards, Phill W.

Options: ReplyQuote

Written By
Re: MYSQL monthly record view
June 20, 2023 02:02AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.