query to get the values in single table

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

0	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

0	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

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

Latest Posts