Click here to Skip to main content
15,890,717 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Here i have two tables
one is Employeedetail consisting (EmpId,Firstname,Lastname,GenderId,Salary) columns and
in the other table i have is tblGender(Id,Gender) consisting Foreignkey relationship.

Here i want to Display Male Records from EmployeeDetail Using Subquery(bu joining Gender colun to the Employeedetail)

i got that by using joins by writing the query as

SQL
Select Firstname,Lastname,Salary,Gender from Employeedetail 
join tblGender
on Employeedetail.GenderId = tblGender.Id
Where Gender ='Male'

but how to do it using a subquery.

i have tried it something like

SQL
Select Firstname,Lastname,Salary,GenderId
From Employeedetail where GenderId in (Select Id from tblGender where Id=1)


but only getting GenderId but i want to display Male & Female instead GenderId Help me Some one
Posted
Updated 8-Mar-20 6:11am
v3

1 solution

Are you looking for something like the following

SQL
Select ed.Firstname,ed.Lastname,ed.Salary,g.Gender
From Employeedetail ed
    cross apply (Select g.Id,g.Gender from tblGender g
                    where g.Id=1
                        and ed.GenderId=g.Id) g
 
Share this answer
 
v2
Comments
raxhemanth 29-Nov-14 1:27am    
Thankyou yusuf but not working
yusuf_ahmed 29-Nov-14 2:01am    
What error you are getting?
raxhemanth 29-Nov-14 2:13am    
Msg 207, Level 16, State 1, Line 5
Invalid column name 'GenderId'.
yusuf_ahmed 29-Nov-14 2:26am    
Sorry for that

Select ed.Firstname,ed.Lastname,ed.Salary,g.Gender
From Employeedetail ed
cross apply (Select g.Id,g.Gender from tblGender g
where g.Id=1
and ed.GenderId=g.Id) g
raxhemanth 29-Nov-14 2:46am    
Great Thankyou Sooooomuch Yousuf it's working excellent
can you pleas tell me what is cross apply ? what is the usage?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900