SQL Statement with "dynamic" where clause [duplicate]

This question already has an answer here: 开发者_如何学编程 Closed 11 years ago.

Possible Duplicate:

Howto build a SQL statement with using IDs that might not be available in the table?

Hello together, I try to build a SQL statement with some unknown variables. I would like to select the province, district, commune and village out of table dbo.pmd. As there are only the IDs stored, I need to read the name out of the value table. This is no problem for province and district as they will always be filled in table dbo.pmd. Anyhow the commune and village is not mandatory and might therefor be not filled. This is where it comes to a problem if I compare the IDs between dbo.pmd and dbo.vhcs (communes) and dbo.pmd and dbo.vhv (villages) if there is no value stored in the dbo.pmd table.

The tables look like this:

table: dbo.pmd

fields: province, district, commune, village (stored value is the ID number)

table: dbo.vhp

fields: country_id, province_id, province_en (name of province)

table: dbo.vhd

fields: province_id, district_id, district_en (name of district)

table: dbo.vhc2

fields: district_id, commune_id, commune_en (name of commune)

table: dbo.vhv

fields: commune_id, village_id, village_en (name of commune)

My current statement looks like this:

SELECT pmd.patient_code, pmd.last_name, pmd.first_name, pmd.age, pmd.sex, pmd.province, pmd.district, pmd.commune, pmd.village 
FROM dbo.pmd AS pmd
INNER JOIN dbo.vhp AS vhp 
ON pmd.province = vhp.province_id
INNER JOIN dbo.vhd AS vhd
ON pmd.district = vhd.district_id 
INNER JOIN dbo.vhc2 AS vhc2
ON (pmd.commune is NULL OR (pmd.commune = vhc2.commune_id)) 
INNER JOIN dbo.vhv AS vhv
ON (pmd.village is NULL OR (pmd.village = vhv.village_id))
ORDER BY pmd.last_name

I have two entries in my database. One entry with commune_id and village_id = NULL and one with commune_id and village_id = some ID number.

I have stopped the execution of this statement after ~2minutes and there are: 8273612 rows all with the same values, showing only the entry with commund_id and village_id equals NULL.

I am using Microsoft SQL Server 2008.

---

    INNER JOIN dbo.vhc2 AS vhc2
ON (pmd.commune is NULL OR (pmd.commune = vhc2.commune_id)) 

You are inadvertently doing something like a cartesian join here. On a row where pmd.commune is NULL, pmd.commune IS NULL evaluates as true for every row in vhc2, causing every single row in vhc2 to be joined to that pmd row.

Your scenario this time around is a little bit different from what you described in your previous question, which is why the solution offered there is not helping you in your current situation.

Some variation on an outer join is what you want for vhc2 & vhv, e.g.:

LEFT OUTER JOIN dbo.vhc2 AS vhc2
ON  pmd.commune = vhc2.commune_id

---

Those two is null checks on join are causing the issue. I think you are looking for left join

SELECT pmd.patient_code, pmd.last_name, pmd.first_name, pmd.age, pmd.sex, pmd.province, pmd.district, pmd.commune, pmd.village 
FROM dbo.pmd AS pmd
    INNER JOIN dbo.vhp AS vhp 
        ON pmd.province = vhp.province_id
    INNER JOIN dbo.vhd AS vhd
        ON pmd.district = vhd.district_id 
    LEFT JOIN dbo.vhc2 AS vhc2
        ON pmd.commune = vhc2.commune_id
    LEFT JOIN dbo.vhv AS vhv
        ON pmd.village = vhv.village_id
ORDER BY pmd.last_name

Side note: you might want to revisit table namings - very difficult to understand the db design.