如何使用表中可能不存在的ID构建SQL语句？ [英] Howto build a SQL statement with using IDs that might not be available in the table?

问题描述

使用Microsoft SQL Server 2008，假设有一个表格1，其中保留了选定的省，地区，公社和村庄的ID。然后是表2，其中包含省，区，公社和村庄的ID和名称。省和地区是必填字段，将始终填写。公社和村庄可能会被填充，但由于不需要，甚至可能不会被填充。

Using Microsoft SQL Server 2008, let's say there is a table1 that keeps the selected ids of provinces, districts, communes and villages. And then there is table2 with the ids and names of provinces, districts, communes and villages. Provinces and districts are required fields and will always be filled. Communes and villages might be filled but might even not be filled as they are not required.

构建动态SQL语句而不知道ID是否为最佳方法是什么？

What is the best way to build a dynamical SQL statement without knowing if the ids for communes and villages are filled in table1 or not.

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
  FROM dbo.table1 AS tbl1 
   AND dbo.table2 AS tbl2 
 WHERE tbl1.province = tbl2.province_id
   AND tbl1.district = tbl2.district_id 
   AND tbl1.commune = tbl2.commune_id 
   AND tbl1.village = tbl2.village_id

此语句给出如果未填写table1中的ID，则结果错误。

This statement gives wrong results if the id in table1 is not filled.

推荐答案

外部联接在这里不起作用，因为您不想拥有table2中的所有元素，而只希望拥有表1中存在相应元素的那些元素。

An OUTER JOIN won't work here, because you don't want to have all elements from table2, but only those where a corresponding element exists in table 1.

想要执行以下操作：

SELECT tbl1.province, tbl1.district, tbl1.commune, tbl1.village 
FROM dbo.table2 AS tbl2 
INNER JOIN dbo.table1 AS tbl1
ON tbl1.province = tbl2.province_id 
AND tbl1.district = tbl2.district_id 
AND (tbl1.commune is NULL OR (tbl1.commune = tbl2.commune_id)) 
AND (tbl1.village is NULL OR (tbl1.village = tbl2.village_id))

这篇关于如何使用表中可能不存在的ID构建SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！