SQL Joins for Multiple Fields with Null Values

I have a table of maintenance requirements and associated monthly frequency it is to be performed

maint

+----------+------+
| maint_id | freq |
+----------+------+
|        1 |    6 |
|        2 |   12 |
|        3 |   24 |
|        4 |    3 |
+----------+------+

I also have a table of equipment with data on its manufacturer, model, device type and building.

equip

+----------+--------+--------+--------+---------+
| equip_id | mfg_id | mod_id | dev_id | bldg_id |
+----------+--------+--------+--------+---------+
|        1 |      1 |      1 |      3 |       1 |
|        2 |      1 |      2 |      3 |       1 |
|        3 |      2 |      3 |      1 |       2 |
|        4 |      2 |      3 |      1 |       3 |
+----------+--------+--------+--------+---------+

I am trying to match each maintenance requirement with its associated equipment. Each requirement applies to a specific manufacturer, model, device, facility or any combination of these in its scope of application. I have created a table to manage these relationships like this:

maint_equip

+----------------+----------+--------+--------+--------+---------+
| maint_equip_id | maint_id | mfg_id | mod_id | dev_id | bldg_id |
+----------------+----------+--------+--------+--------+---------+
|              1 |        1 | NULL   | NULL   | 1      | NULL    |
|              2 |        2 | 2      | NULL   | NULL   | 2       |
|              3 |        3 | NULL   | NULL   | NULL   | 1       |
|              4 |        3 | NULL   | NULL   | NULL   | 3       |
|              5 |        4 | 1      | NULL   | 3      | 1       |
+----------------+----------+--------+--------+--------+---------+

As per the table above, requirement 1 would only apply to any equipment having device type “1.” Requirement 2 would apply to all equipment having both manufacturer “2” AND building “2.” Requirement 3 would apply to all equipment having building “1” OR building “3” Requirement 4 would apply to equipment having all of mfg_id “1” AND dev_id “3” AND building “1.”

I am trying to write a query to give me a list of all equipment ids and all the associated frequency requirements based on the relationships defined in maint_equip. The problem I’m running into is handling the multiple joins. I have already tried:

SELECT  equip.equip_id, maint.freq
FROM    equip INNER JOIN
    maint_equip ON equip.mfg_id = maint_equip.mfg_id 
        OR equip.mod_id = maint_equip.mod_id 
        OR equip.dev_id = maint_equip.dev_id 
        OR equip.bldg_id = maint_equip.bldg_id INNER JOIN
    maint ON maint_equip.maint_id = maint.maint_id

but separating multiple joins using OR means that it is not accounting for the AND contingencies of each row. For example, maint_id 2 should only apply to equip_id 3 but ids 3 and 4 are both returned. If AND is used, then no rows are returned because none have a value for all columns.

Is it possible to join the tables in such a way to accomplish this or is there another way to structure the data?

Answer

If I get this right, when an equipment related ID in maint_equip is null, that should count as a match. Only if it isn’t null, it must match the respective ID in equip. That is, you want to check if an ID in maint_equip is null or equal to its counterpart from equip.

SELECT e.equip_id,
       m.freq
       FROM equip e
            INNER JOIN maint_equip me
                       ON (me.mfg_id IS NULL
                            OR me.mfg_id = e.mfg_id)
                          AND (me.mod_id IS NULL
                                OR me.mod_id = e.mod_id)
                          AND (me.dev_id IS NULL
                                OR me.dev_id = e.dev_id)
                          AND (me.bldg_id IS NULL
                                OR me.bldg_id = e.bldg_id)
            INNER JOIN maint m
                       ON m.maint_id = me.main_id;