SQL Joins for Multiple Fields with Null Values

joins with null values examples
sql join include null
inner join with null values
include null values in sql
self join with null values
sql join missing values
sql join if null
isnull in join condition sql server

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

Join SQL Server tables where columns include NULL values, The screen shot below shows that the column does not allow NULLs. query results. Example 2: When these two options are ON the default  When building database tables you are faced with the decision of whether to allow NULL values or to not allow NULL values in your columns. By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set.


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 )

How null values affect joins, Since bit columns do not permit null values, a value of 0 appears in an outer join null values represent unknown or inapplicable values, Transact-SQL has no  SQL Joins for Multiple Fields with Null Values. Then you have to account for ties where there are multiple maint_id values that match an equal number of times


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
   ; 

Joining Tables Survival Guide, Columns containing NULL do not match any values when you are creating an inner joins only two tables, FROM clauses can contain multiple join specifications. This Transact-SQL query finds the titles of all books of a particular type and  Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues.


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 |
+----------+----------+------+

Joining on NULLs in SQL SERVER, SQL Server prevents two rows from being joined together using the regular join syntax if the column values contain NULLs. Here's how to fix  SQL Server ISNULL () With Multi Column Names Many a times we come across null values within tables in SQL Server. Null values are the values with no data; that means the data is missing or unknown.


Sql join multiple columns from different tables, Sql join multiple columns from different tables. By default SQL Server sets the column value to allow NULL values when creating new tables, unless other  The column contains the values 1, 2, and 3 in table T1, while the column contains NULL, 2, and 3 in table T2. Inner JOINing these tables on the column TestJoin returns two rows, since you cannot


An Illustrated Guide to Multiple Join, Join Types in SQL Queries A multiple join is a use of more than one join in a single query. Empty columns will be filled with NULL values. There are 2 types of joins in the MySQL: inner join and outer join. The difference is outer join keeps nullable values and inner join filters it out. So I’ll show you examples of joining 3 tables in MySQL for both types of join. How To Inner Join Multiple Tables


How to LEFT JOIN Multiple Tables in SQL, However, first make sure that your second table doesn't have records with NULL values in all columns except for the common field used for  Concatenate multiple columns in SQL Server with NULL value When we need to concatenate two columns simply we can use + sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem.