条件内连接 [英] condition an inner join

问题描述

我的数据库内置如下：

非活动表 - 包括所有已删除的\ Inactive用户
活动表 - 包括所有当前活跃用户

主表 - 包括所有用户（活动和非活动）



some所有三个表中存在字段（例如userName），我想从非活动表或活动表中获取数据（因为所有表中的数据并不总是相等），所以我希望有一个连接，可以来自Active表或Inactive表（根据主表中的IsActive字段）

  SELECT  M.ID，A.userName 
  FROM  MainTbl M 
  IF  M.IsActive =  1  
 那么 
  INNER   JOIN 有效Tbl A  ON  A.UserID = M.ID 
  ELSE  
  INNER   JOIN  InactiveTbl A  ON  A.UserID = M. ID 

注意在两个加入中我都使用A



是有没有办法做这样的事情？

解决方案

是的，这是可能的，但你必须做一个 UNION 。

像这样：

  SELECT  
 M.ID 
，A.Username 
  FROM  MainTbl M  INNER   JOIN  ActiveTbl A  ON  A.UserID = M.ID 
  WHERE  M.IsActive =  1  
 
  UNION  
 
  SELECT  
 M.ID 
，A.Username 
  FROM  MainTbl M  INNER   JOIN  InactiveTbl A  ON  A.UserID = M.ID 
  WHERE  M.IsActive =  1  

希望这会有所帮助。无论如何，感谢投票。

问候。

HI，



将您的查询更改为动态查询如：

  DECLARE   @SqlQuery   VARCHAR （MAX）
 
 
  set   @ SqlQuery  = '  SELECT M.ID， A.userName 
来自MainTbl M' 
 如果 存在（ 选择 M.IsActive 来自 MainTbl M）
 开始 
  set   @ SqlQuery  =  @SqlQuery  + '  INNER JOIN ActiveTbl A ON A.UserID = M.ID' 
  end  
 其他 
 开始 
  set   @ SqlQuery  =  @ SqlQuery  + '   INNER JOIN InactiveTbl A ON A.UserID = M.ID' 
  end  
  exec   @ SqlQuery  

感谢

Hi,

I have a DB built as following:
Inactive Table - including all deleted\Inactive users
Active Table - including all currently active users
Main Table - including all users (both active and inactive)

some fields exists in all three tables (Such as userName), and I would like to take the data from the Inactive or Active table (Since the data is not always equal in all of the tables), so I want to have a join which will be either from the Active table or Inactive table (according to IsActive field in the main table)

SELECT M.ID, A.userName 
FROM MainTbl M
IF M.IsActive = 1
THEN
    INNER JOIN ActiveTbl A ON A.UserID = M.ID
ELSE
    INNER JOIN InactiveTbl A ON A.UserID = M.ID

Pay attention that in both join I use A

Is there any way to do something like that?

解决方案

Yes this is possible, but you have to do an UNION.
Like this :

SELECT
   M.ID
  ,A.Username
FROM MainTbl M INNER JOIN ActiveTbl A ON A.UserID = M.ID
WHERE M.IsActive = 1

UNION

SELECT
   M.ID
  ,A.Username
FROM MainTbl M INNER JOIN InactiveTbl A ON A.UserID = M.ID
WHERE M.IsActive = 1

Hope this helps. Thanks to vote anyway.
Regards.

HI,

Change your query to a dynamic query like:

DECLARE @SqlQuery VARCHAR(MAX)


set @SqlQuery = 'SELECT M.ID, A.userName
                 FROM MainTbl M'
                 if exists(select M.IsActive from MainTbl M)
                 begin
               set @SqlQuery = @SqlQuery + 'INNER JOIN ActiveTbl A ON A.UserID = M.ID'
                   end
                 else
                 begin
                  set @SqlQuery = @SqlQuery + 'INNER JOIN InactiveTbl A ON A.UserID = M.ID'
                   end
exec @SqlQuery

Thanks

这篇关于条件内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！