嵌套SQL语句以创建具有多个表中的字段的Gridview

Question

我有一个搜索页面，用户将在其中选择社区名称，按下搜索按钮后，网格视图将填充，显示该社区中所有帐户的所有者名称、居民名称和地址。

我有以下表格：社区、地址、业主、居民。地址具有CommunityID的外键。所有者和居民各有一个AddressID的外键。

使用SQL语句

SELECT 
     Addresses.AddressID 
FROM 
     Addresses 
     INNER JOIN Communities ON Addresses.CommunityID = Communities.CommunityID 
WHERE 
     Addresses.CommunityID = '1'    

我从第一个社区得到了一个适当的AddressID列表。从那里，我想列出属于每个AddressID的所有者、居民和实际地址。

我觉得这应该有效：

SELECT        
     Owners.OwnLName, 
     Owners.OwnFName, 
     Residents.ResLName, 
     Residents.ResFName, 
     Addresses.Address
FROM            
     Addresses 
     INNER JOIN Owners ON Addresses.AddressID = Owners.AddressID 
     INNER JOIN Residents ON Owners.OwnerID = Residents.OwnerID 
WHERE 
     Owners.AddressID = ALL 
                        (
                           SELECT 
                               Addresses.AddressID 
                           FROM 
                               Addresses 
                               INNER JOIN Communities ON Addresses.CommunityID =Communities.CommunityID            
                               WHERE Addresses.CommunityID = '1'
                        )

我已经搞了这么长时间了，似乎无法思考如何正确地制定这个方案。

任何帮助都将不胜感激！！

Answer 1

您可以这样做。

SELECT        
     Owners.OwnLName, 
     Owners.OwnFName, 
     Residents.ResLName, 
     Residents.ResFName, 
     Addresses.Address
FROM            
     Addresses 
     INNER JOIN Owners ON Addresses.AddressID = Owners.AddressID 
     INNER JOIN Residents ON Owners.OwnerID = Residents.OwnerID 
WHERE 
     Owners.AddressID IN 
                        (
                           SELECT 
                               Addresses.AddressID 
                           FROM 
                               Addresses 
                               INNER JOIN Communities ON Addresses.CommunityID =Communities.CommunityID            
                           WHERE Addresses.CommunityID = '1'
                        )