请教关于SQL多表关联的有关问题
请问关于SQL多表关联的问题
A表中的ID与B表中的ID关联,C表中有jobno字段与B表中的jobno关联,但C表有可能是空表
,当C表没有记录时,只要求查出a.id = b.id 的记录
select a.*
from JobMain a (nolock),JobDetail b (nolock),jobnotable c (nolock)
and a.id = b.id
and isnull(b.jobno,'') = c.jobno or ??
------解决方案--------------------
看看是不是你要的结果:
------解决方案--------------------
刚给你的代码有误,请参照下面的吧:
A表中的ID与B表中的ID关联,C表中有jobno字段与B表中的jobno关联,但C表有可能是空表
,当C表没有记录时,只要求查出a.id = b.id 的记录
select a.*
from JobMain a (nolock),JobDetail b (nolock),jobnotable c (nolock)
and a.id = b.id
and isnull(b.jobno,'') = c.jobno or ??
------解决方案--------------------
看看是不是你要的结果:
select COUNT(*) from jobnotable with(nolock)
if @@ROWCOUNT =0
select
a.*
from
JobMain a with(nolock),
JobDetail b with(nolock)
where
a.id = b.id
else
select
a.*
from
JobMain a with(nolock),
JobDetail b with(nolock),
jobnotable c with(nolock)
where
a.id = b.id
and b.jobno=c.jobno
------解决方案--------------------
刚给你的代码有误,请参照下面的吧:
declare @rowcount int
set @rowcount=0
select @rowcount=COUNT(*) from jobnotable with(nolock)
if @ROWCOUNT =0
select
a.*
from
JobMain a with(nolock),
JobDetail b with(nolock)
where
a.id = b.id
else
select
a.*
from
JobMain a with(nolock),
JobDetail b with(nolock),
jobnotable c with(nolock)
where
a.id = b.id
and b.jobno=c.jobno