基于条件对额外列进行 SQL 连接

发布于 2024-12-04 04:09:57 字数 603 浏览 1 评论 0原文

我想知道如何根据条件向联接添加额外的列。

我正在尝试做类似下面概述的事情。如果一个位置是一个县，那么我想要该县的所有房产；但是，如果地点是一个城镇，我想要该城镇的房产。不幸的是，城镇代码可能会在县之间重复，因此我需要按县和城镇代码进行过滤。

SELECT DISTINCT [PropertyID] 
FROM PropertyLocations
LEFT JOIN [dbo].[Locations]
   ON [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
 -- IF / CASE locations.[LocationLevel] is 6 then 
 -- i want to join on a second column as well
 -- [PropertyLocations].[CountySubCode] = [Locations].[SubCountyCode]
WHERE [LocationName] = 'county/town name'
AND [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
order BY [PropertyID]

原文

I want know how I can add an extra column to a Join based on a condition.

I am trying to do something like outline below. If a location is a county then I want all properties in the county; however, if the location is a town I want the properties in that town. Unfortunately town codes can be duplicated across counties, so I need to filter by county and town code.

SELECT DISTINCT [PropertyID] 
FROM PropertyLocations
LEFT JOIN [dbo].[Locations]
   ON [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
 -- IF / CASE locations.[LocationLevel] is 6 then 
 -- i want to join on a second column as well
 -- [PropertyLocations].[CountySubCode] = [Locations].[SubCountyCode]
WHERE [LocationName] = 'county/town name'
AND [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
order BY [PropertyID]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玩世 2024-12-11 04:09:57

这是你需要的吗？

   LEFT JOIN [dbo].[Locations]
     ON [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
        AND ( [Locations].[LocationLevel] <> 6
               OR [PropertyLocations].[CountySubCode] =
                  [Locations].[SubCountyCode]
            )

Is this what you need?

   LEFT JOIN [dbo].[Locations]
     ON [PropertyLocations].[CountyCode] = [Locations].[CountyCode]
        AND ( [Locations].[LocationLevel] <> 6
               OR [PropertyLocations].[CountySubCode] =
                  [Locations].[SubCountyCode]
            )

回复收藏 0 原文

~没有更多了~