如何使用子查询连接两个表？ [英] How to join two tables using a subquery?

问题描述

这里我有两张桌子

一个是Employeedetail组成（EmpId，Firstname，Lastname，GenderId，Salary）列和

在另一张表中我有tblGender（ ID，性别）包括外键关系。



这里我想使用子查询显示来自EmployeeDetail的男性记录（将性别colun加入Employeedetail）



i通过编写查询来使用连接得到的结果

 选择名字，姓氏，薪水，性别来自 Employeedetail 
  join  tblGender 
  on  Employeedetail.GenderId = tblGender.Id 
 其中性别= < span class =code-string>' 男性' 

但该怎么做使用子查询。



i尝试了类似

选择名字，姓氏，薪水，GenderId
来自 Employeedetail < span class =code-keyword>其中 GenderId in （选择 Id 来自 tblGender 其中 Id = 1）

但只获得GenderId，但我想展示男性&女性代替GenderId帮帮我一些

解决方案

您是否正在寻找以下内容

 选择 ed.Firstname，ed.Lastname，ed.Salary，g.Gender 
 来自 Employeedetail ed 
  cross   apply （选择 g.Id，g.Gender 来自 tblGender g 
 其中​​ g.Id = 1 
 和 ed.GenderId = g.Id）g 

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

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

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

解决方案

Are you looking for something like the following

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

这篇关于如何使用子查询连接两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！