SQL QUERY Question........................

Question

4.50/5 (2 votes)

See more:

I have two tables,
first one is "tbl_login" which contains two columns "userid' and "name" (both are varchar fields)
second one is "tbl_leave" which conatins three columns "userid","fromdate","duration" (all are varchar fields)
tbl_login.userid=tbl_leave.userid

for example
tbl_login :

userid name
001 abcd
002 defc

tbl_leave

userid fromdate duration
001 2011/03/08 2 days
002 2011/03/28 5 days

i want a query to display like this

userid name status
001 abcd absent
002 defc present

display all names in tbl_login first,then check any userid having today date in fromdate column of tbl_leave
if existing then Absent,else Present to be displayed

Please help

Posted 7-Mar-11 18:29pm

kishore Rajendran

Updated 7-Mar-11 18:40pm

v3

Add a Solution

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)

pankajupadhyay29 · Accepted Answer · 2011-03-07T19:35:00

Solution 1

Try this--

SQL

select userid, name, (Case when Exist(Select 1 from tbl_leave where tbl_leave.UserID=tbl_login.userid and Convert(varchar(11),tbl_leave.fromdate,106)=convert(varchar(11),getdate(),106)) then 'absent' else 'present' end) as status from tbl_login

--Pankaj

Posted 7-Mar-11 19:35pm

pankajupadhyay29

Comments

kishore Rajendran 10-Mar-11 1:13am

i want to check that tbl.leave.fromdate in a date range...i tried but getting this error
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Please help

pankajupadhyay29 10-Mar-11 1:19am

what query your writing??

kishore Rajendran 10-Mar-11 1:22am

SELECT tbl_login.userid,name,ISNULL ((SELECT 'Absent ' AS Expr1 FROM tbl_leave WHERE (userid = tbl_login.userid) AND fromdate = '2011/03/10'), 'Present'),ISNULL ((SELECT type AS Expr1 FROM tbl_leave WHERE (userid = tbl_login.userid) AND fromdate = '2011/03/10'), null) as status from tbl_login

kishore Rajendran 10-Mar-11 1:30am

SELECT tbl_login.userid,name,ISNULL ((SELECT 'Absent ' AS Expr1 FROM tbl_leave WHERE (userid = tbl_login.userid) AND '2011/03/10' between '2011/03/10' and '2011/07/03'), 'Present'),ISNULL ((SELECT type AS Expr1 FROM tbl_leave WHERE (userid = tbl_login.userid) AND fromdate = '2011/03/10'), null) as status from tbl_login

pankajupadhyay29 10-Mar-11 1:43am

use case when with exist to achieve this as i used in the solution for otherwise whenever it is returning multiple values it will give error.