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 : 1532 | Status : [Member] | Replies : 4
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response