Server R2中的多表内连接

Question

我对INNER JOIN有问题。有三个表Customer，Regular aAnd Master。使用此查询，我只从customer表中获取记录

查询

Select 
    Customer.CustomerId, 
    Customer.RefId, 
    Regular.LicenseId, 
    Master.MasterId, 
    Master.FullName, 
    Master.Profile 
From 
    Customer 
Inner Join 
    Regular on Customer.RefId = Regular.Licenseid 
Inner join 
    master on regular.controlid = master.masterid Or customer.refid = master.masterid

结果

​

​

在表Customer中，具有记录M000001和R000001的RefId。为什么结果仅从RefId开始显示R000001？

来自客户主的数据源是..。

结果将包括客户表中的所有行.

​

​

任何人都可以解决这个问题并编辑我的查询，以便从customer表中获得两种类型的记录.

Answer 1

因此，从这个问题来看，在联接中使用OR条件似乎是个坏主意：

https://stackoverflow.com/a/5901901/3915817

因此，我将直接切换查询。您可以使用外部连接来完成这个任务。

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Coalesce(Mast1.MasterId, Mast2.MasterId) AS MasterId,
       Coalesce(Mast1.FullName, Mast2.FullName) AS FullName,
       Coalesce(Mast1.Profile, Mast2.Profile) AS Profile
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Left Outer Join master As Mast1 
        on regular.controlid = master.masterid
    Left Outer Join master As Mast2
        on customer.refid = master.masterid
Where (Mast1.masterid Is Not Null
    Or Mast2.MasterId Is Not Null)

或者有一个联合条款

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Master.MasterId, 
       Master.FullName, 
       Master.Profile 
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Inner Join master on regular.controlid = master.masterid

Union

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Master.MasterId, 
       Master.FullName, 
       Master.Profile 
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Inner Join master on customer.refid = master.masterid