query to select list of employees who do not have phone numbers

Posted by Udaysimha under Sql Server on 1/19/2012 | Points: 10 | Views : 3119 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,
I have two tables that are : 1) Emp_Details (Emp_Id, Emp_name, Gender)
2) Emp_phone _details (Emp_id, Phone_type, Phone_no)
Here my requirement is to get list of employee id and names who do not have phone number.
please any body let me know the query for above requirement.
Thanks

uday

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksamantaray on: 1/19/2012 [Member] Silver | Points: 25

0	select Employee_Details.emp_id,Employee_Details.emp_name from Employee_Details where emp_id not in (select emp_id from Employee_PhoneDetails where phoneNo is not null) Thanks, Sanjay Udaysimha, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pkanwar on: 1/20/2012 [Member] Starter | Points: 25

0	select a.Emp_id, a.Emp_name from Employee_Details as a inner join Emp_phone_details as b on a.Emp_id = b.Emp_id where b.Phone_no is null Udaysimha, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 1/20/2012 [Member] Silver | Points: 25

0	Dear PKanwar, Your query is not giving required output , Please Rectify: Giving you a situation: *select from Employee_Details Output is: emp_id emp_name emp_Address emp_Salary 1 sanjay cuttack 20000 2 julie bbsr 25000 3 santwana bbsr 30000 4 ss ss 33 5 sanjay bangalore 4444 6 sanjay bangalore 4444 7 ss sss 3333 8 ss sss 3333 select * from Employee_PhoneDetails Output is: id emp_id phoneTeype phoneNo 1 4 mobile 111111111 2 4 Land 11351111 select Employee_Details.emp_id,Employee_Details.emp_name from Employee_Details where emp_id not in (select emp_id from Employee_PhoneDetails where phoneNo is not null) Output is:** emp_id emp_name 1 sanjay 2 julie 3 santwana 5 sanjay 6 sanjay 7 ss 8 ss Your Query : select a.Emp_id, a.Emp_name from Employee_Details as a inner join Employee_PhoneDetails as b on b.Emp_id = a.Emp_id where b.phoneNo is null Returns 0 rows. Please Rectify this. Thanks, Sanjay Thanks, Sanjay Udaysimha, if this helps please login to Mark As Answer. \| Alert Moderator

Dear PKanwar,
Your query is not giving required output , Please Rectify:
Giving you a situation:

select * from Employee_Details
Output is:
emp_id emp_name emp_Address emp_Salary
1 sanjay cuttack 20000
2 julie bbsr 25000
3 santwana bbsr 30000
4 ss ss 33
5 sanjay bangalore 4444
6 sanjay bangalore 4444
7 ss sss 3333
8 ss sss 3333

select * from Employee_PhoneDetails
Output is:
id emp_id phoneTeype phoneNo
1 4 mobile 111111111
2 4 Land 11351111

select Employee_Details.emp_id,Employee_Details.emp_name from Employee_Details where emp_id not in
(select emp_id from Employee_PhoneDetails where phoneNo is not null)
Output is:
emp_id emp_name
1 sanjay
2 julie
3 santwana
5 sanjay
6 sanjay
7 ss
8 ss

Your Query :
select a.Emp_id, a.Emp_name from Employee_Details as a inner join Employee_PhoneDetails as b on b.Emp_id = a.Emp_id
where b.phoneNo is null
Returns 0 rows.

Please Rectify this.

Thanks,
Sanjay

Thanks,
Sanjay

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

Posted by: Niladri.biswas on: 2/8/2012 [Member] Platinum | Points: 25

0	Declare @Emp_Details table(Emp_Id int identity, Emp_name varchar(20), Gender varchar(1)) Insert into @Emp_Details Select 'sanjay','M' union all Select 'julie','F' union all Select 'santwana','M' union all Select 'ss ss','M' union all Select 'sanjay345','M' Declare @Emp_phone_details table(Emp_id int, Phone_type varchar(20), Phone_no int) Insert into @Emp_phone_details Select 1,'mobile', 111111111 union all Select 2,'Land', 11351111 Select ed.Emp_Id, ed.Emp_name From @Emp_Details ed Join @Emp_phone_details epd on ed.Emp_Id =epd.Emp_Id Where len(epd.Phone_no)>0 Output --------- Emp_Id Emp_name 1 sanjay 2 julie Best Regards, Niladri Biswas Udaysimha, if this helps please login to Mark As Answer. \| Alert Moderator

Declare @Emp_Details table(Emp_Id int identity, Emp_name varchar(20), Gender varchar(1)) 

Insert into @Emp_Details 

				Select 'sanjay','M' union all Select 'julie','F' union all

				Select 'santwana','M' union all Select 'ss ss','M' union all

				Select 'sanjay345','M' 



Declare @Emp_phone_details table(Emp_id int, Phone_type varchar(20), Phone_no int) 

Insert into @Emp_phone_details 

				Select 1,'mobile', 111111111 union all

				Select 2,'Land', 11351111 



Select

	ed.Emp_Id, 

	ed.Emp_name

From @Emp_Details ed

Join @Emp_phone_details epd on ed.Emp_Id =epd.Emp_Id

Where len(epd.Phone_no)>0

Output
---------
Emp_Id Emp_name
1 sanjay
2 julie

Best Regards,
Niladri Biswas

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

Latest Posts