SQL processing invalid foreign keys in the Where clause

Question

Suppose I have a table called my_table with primary_key table_pk and with three fuzzy fields (foreign keys) with the names X_fk, Y_fk and Z_fk, respectively, and another field called data. Tables X, Y, and Z have a primary key field and a name field (for example, {X_pk, X_name}, {Y_pk, Y_name}, {Z_pk, Z_name}).

I need a query that uniquely returns a row from my_table, given the names in tables X, Y and Z.

SELECT table_pk 
FROM my_table 
WHERE 
    X_fk = (SELECT X_pk FROM X WHERE X_name = ?) 
  AND 
    Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = ?) 
  AND 
    Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = ?)

This does not work when I want to find the string (X_Name, Y_Name, Z_Name) = ('XXX', 'YYY', NULL), because Z_fk = NULL can never result in TRUE. How can I modify the above query to get a unique record from a table that takes into account unique records, where some foreign keys are NULL?

Answer 1

Try the following:

SELECT min(table_pk)
FROM my_table 
WHERE 
    ((X_fk = (SELECT X_pk FROM X WHERE X_name = ?)) OR (? IS NULL AND X_fk IS NULL))
AND 
    ((Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = ?)) OR (? IS NULL AND Y_fk IS NULL)) 
AND 
    ((Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = ?)) OR (? IS NULL AND Z_fk IS NULL))

Answer 2

Same answer as LukLed, but with input associated only once from the front end

SELECT table_pk 
FROM my_table
cross join (select ? iX, ? iY, ? iZ) i
WHERE 
    ((i.iX is null and X_fk is null) OR X_fk = (SELECT X_pk FROM X WHERE X_name = i.iX))
  AND 
    ((i.iY is null and Y_fk is null) or Y_fk = (SELECT Y_pk FROM Y WHERE Y_name = i.iY))
  AND 
    ((i.iZ is null and Z_fk is null) or Z_fk = (SELECT Z_pk FROM Z WHERE Z_name = i.iZ))

This looks shorter, but does not make any good indexes (unless -1 is a valid value).

SELECT table_pk 
FROM my_table
WHERE 
    Isnull(X_fk,-1) = isnull((SELECT X_pk FROM X WHERE X_name = ?),-1)
  AND 
    Isnull(Y_fk,-1) = Isnull((SELECT Y_pk FROM Y WHERE Y_name = ?),-1)
  AND 
    Isnull(Z_fk,-1) = Isnull((SELECT Z_pk FROM Z WHERE Z_name = ?),-1)