SQL Joins for Multiple Fields with Null Values

Question

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?

Possible duplicate of [SQL Server Query: Using JOIN to include NULL values](https://stackoverflow.com/questions/24227728/sql-server-query-using-join-to-include-null-values) — noraj, Jan 28 '19 at 23:13

sticky bit · Accepted Answer · 2021-06-06T16:53:21.157

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;

This is exactly what I was looking for. Thank you! – user10981012 Jan 29 '19 at 01:52 — user10981012, Jan 29 '19 at 01:52

score 0 · Answer 2 · answered Jan 28 '19 at 23:15

Try this:

   ( equip.mfg_id  = maint_equip.mfg_id  OR maint_equip.mfg_id  is null )
AND( equip.mod_id  = maint_equip.mod_id  OR maint_equip.mod_id  is null )
AND( equip.dev_id  = maint_equip.dev_id  OR maint_equip.dev_id  is null )
AND( equip.bldg_id = maint_equip.bldg_id OR maint_equip.bldg_id is null )

score 0 · Answer 3 · answered Jan 28 '19 at 23:31

Pay attention that your mod_id is always null. Otherwise query below goes through all your cases.

  SELECT  maint_equip.maint_id, equip.equip_id, maint.freq
FROM    equip INNER JOIN
    maint_equip ON (
    (equip.mfg_id = maint_equip.mfg_id AND 
        equip.dev_id = maint_equip.dev_id AND
        equip.bldg_id = maint_equip.bldg_id
     ) OR
     (equip.mfg_id = maint_equip.mfg_id AND 
        maint_equip.dev_id is NULL AND
        equip.bldg_id = maint_equip.bldg_id
     ) OR
     (maint_equip.mfg_id is NULL AND 
        equip.dev_id = maint_equip.dev_id AND
        maint_equip.bldg_id is NULL
     ) OR 
     (maint_equip.mfg_id is NULL AND
        maint_equip.dev_id is NULL AND
        equip.bldg_id = maint_equip.bldg_id
     ) )


      INNER JOIN
    maint ON maint_equip.maint_id = maint.maint_id
   ;

score 0 · Answer 4 · answered Jan 29 '19 at 00:08

It seems to me that what you're actually looking for is the maintenance schedule that has the highest number of matches. You can get that by using a SUM with a series of CASE expressions to get the count of matching columns.

Then you have to account for ties where there are multiple maint_id values that match an equal number of times. For the example below, I opted to use maintenance frequency as the tie breaker, favoring more frequent maintenance over less frequent maintenance.

Rextester link with data set up: https://rextester.com/VISR88105

The ROW_NUMBER in the ORDER BY clause sorts the results by number of column matches (the nutty SUM/CASE combo) in descending order to get the most matches first, and then by maintenance frequency in ascending order to favor more frequent maintenance. Easy to reverse that with a DESC if you like. Then the TOP (1) WITH TIES limits the result set to all of the rows where ROW_NUMBER evaluates to 1.

The code:

SELECT TOP (1) WITH TIES
  e.equip_id, 
  m.maint_id, 
  m.freq
FROM 
  #maint as m
JOIN
  #maint_equip as me
    ON
      m.maint_id = me.maint_id
JOIN
  #equip as e
    ON 
      e.mfg_id = COALESCE(me.mfg_id, e.mfg_id)
      AND
      e.mod_id = COALESCE(me.mod_id, e.mod_id)
      AND
      e.dev_id = COALESCE(me.dev_id, e.dev_id)
      AND
      e.bldg_id = COALESCE(me.bldg_id, e.bldg_id)
GROUP BY 
  e.equip_id, 
  m.maint_id, 
  m.freq
ORDER BY 
  ROW_NUMBER() OVER (PARTITION BY e.equip_id ORDER BY (
    SUM( 
    (CASE WHEN e.mfg_id = me.mfg_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.mod_id = me.mod_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.dev_id = me.dev_id THEN 1 ELSE 0 END) +
    (CASE WHEN e.bldg_id = me.bldg_id THEN 1 ELSE 0 END)) ) DESC, m.freq )

Results:

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

SQL Joins for Multiple Fields with Null Values

4 Answers4