query to get the values in single table

Posted by Kavi.Sirius under Sql Server on 11/3/2012 | Points: 10 | Views : 1006 | Status : [Member] | Replies : 6
hi,

i have a two table like

table 1:
empid date io
1 29/12/12 Present

table 2:
empid date permission
1 29/12/12 1
1 28/12/12 1

i need this type result

empid date details
1 29/12/12 present
1 28/12/12 permission

in 29th date are present in both IO and Permission table
so i need to get the date details from IO table only not in Permission table

i am using union and not in to get the answer,
apart from union and not in how can i get the result


Regards
Kavi.n



Responses

Posted by: Saratvaddilli on: 11/3/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
can you please explain your question , its little bit confusing me

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kavi.Sirius on: 11/3/2012 [Member] Starter | Points: 25

Up
0
Down
i have a two table like

table 1:
empid date io
1 29/12/12 Present

table 2:
empid date permission
1 29/12/12 1
1 28/12/12 1

i need this type result

empid date details
1 29/12/12 present
1 28/12/12 permission

output explanation:

in this table same employee have permission and io details in same date(29/12/12),but i need to get the details from IO table
if i am not use the not in operator in below query it shows the three rows value
because of using union operator,

apart from not in operator how can i get the result,

select empid,date,details from table1
union
select empid,date,details from table2 where date not in (select date from table1)






Regards
Kavi.n

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 11/3/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
kindly check your tables format
the table you have given and the quires you are trying to do on that are contradicting , you don't have details column in the two columns.

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 11/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
the datatype of permission is int and io is varchar.. we can union these two columns. and also there is no details column in table1 as well as table2..
r u trying to find out the employee's who were present or present and took permission details.

Awesome Coding !! :)

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kavi.Sirius on: 11/5/2012 [Member] Starter | Points: 25

Up
0
Down
hi vasanth,

its just a sample coding to understand ok.

Regards
Kavi.n

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 11/5/2012 [Member] Starter | Points: 25

Up
0
Down
hi Kavi.Sirius,..

Please try the following query provided the table 1 column io has similar datatype with table 2 column permission and
in the following query i have taken int datatype for both columns respectively....

Select 

case when A.empid is null then B.empid else A.empid end empid,
B.date, case when A.io=B.permission then 'Present' when A.io is null then 'Permission'
else 'Present' end details
From table_1 A
full outer join table_2 B on A.date=B.date
and isnull(A.empid,B.empid)=isnull(B.empid,A.empid)


Please let me know if there is any problem in executing this query and if it doesn't match your result.....


Sourabh07

Kavi.Sirius, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response