How Do I Get Employee Name With Presenty

Question

0.00/5 (No votes)

See more:

I have 2 TABLES
employee_master

emp_id  emp_name    STATUS  createddate createdby   updateddate updatedby
1       Kiran       1       2015-11-13  1           2015-11-13  1
2       Praju       1       2015-11-13  1           2015-11-13  1
3       e           0       2015-11-13  1           2015-11-13  1
4       Amol        1       2015-11-13  1           2015-11-13  1
5       Kanchan1    1       2015-11-14  1           2015-11-14  1
6       Archana     1       2015-11-18  1           2015-11-18  1

attendance_master

att_id  emp_id  today_date  presenty    plant_id
1       2       2015-11-26  A           2
17      1       2015-11-26  H           1
18      4       2015-11-26  P           1

IF i search FOR 2015-11-26 THEN i should get

emp_id  emp_name    presenty
1       Kiran       H
5       Kanchan1    
6       Archana

AND
IF i search FOR 2015-11-27 THEN i should get

emp_id  emp_name    presenty
1       Kiran
2       Praju
4       Amol
5       Kanchan1
6       Archana

My QUERY IS :

SELECT employee_master.emp_id,employee_master.emp_name,IFNULL(attendance_master.presenty,'') AS presenty,attendance_master.today_date
FROM employee_master LEFT JOIN attendance_master ON attendance_master.emp_id=employee_master.emp_id
WHERE employee_master.Status=1 AND
attendance_master.today_date='2015-11-26'

[edit]Code block added - OriginalGriff[/edit]

Posted 25-Nov-15 21:03pm

Member 12167852

Updated 25-Nov-15 21:36pm

OriginalGriff

v2

Add a Solution

Comments

Richard MacCutchan 26-Nov-15 3:19am

And?

F-ES Sitecore 26-Nov-15 4:25am

You'll need to explain the logic behind why searching for those dates gives those results.

Sums Mohs Eds 27-Nov-15 7:24am

What is the output you are getting with the query you have mentioned here?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-11-30T04:49:00

Your expected results are completely wrong given the data you have presented

If you run your query with 2015-11-26 then there are only 3 entries on attendance_master for that date:

emp_id emp_name presenty today_date
1      Kiran    H        2015-11-26
2      Praju    A        2015-11-26
4      Amol     P        2015-11-26

Kanchan1 and Archana do not have any records in attendance_master - for any date.

If you run the query for 2015-11-27 then you will get nothing - from the data you have presented - as there are no records at all for that date. Remove the check on date however and you get closer to the results you expect.

In other words, your query is working fine.

The following article may be of use Visual Representation of SQL Joins[^]