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?
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;