How to create an SQL statement using identifiers that may not be available in the table?

Question

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

What is the best way to create a dynamic SQL statement without knowing if the identifiers for the communes and villages in table1 are full 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 

This statement gives incorrect results if the identifier in table1 is not populated.

Answer 1

An OUTER JOIN will not work here, because you do not want to have all the elements from table2, but only those where the corresponding element exists in table 1.

You would like to do something like this:

 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))