How to determine if there is a missing value in joined table-CodePudding

I am trying to find the "users" that are missing a value in a 2nd table with the value of column "A" = 16 and then column "B" = 0.

I am looking for these values because that would give me the ability to run a query adding a row for each user that is missing the row with the values of A = 16 and B = 0.

So here is the relevant structure of the tables that we would be joining on.

There are two tables, table 1 and table 2

Table 1

ID
parent id

table 2

table1_id
A
B

The problem I am running into is that table2 can have records associated with the table1_id but still needing to verify if the table2 if there is not a row with table1_id, A missing value 16 while B is missing value 0.

Here is the current idea I am working off of for the sql query

SELECT 
    *
FROM
    table1
        LEFT JOIN
    table2 ON table1.id = table2.table1_id
WHERE
    table1.id IS NOT NULL
        AND table2.id IS NULL;

This will give me all the table1_ids that are missing records from table2 but does however would not pull the rows where there are rows for the table1_id but however does not determine if there are missing rows with the column A with value 16 or Column B = 0.

If you are able to answer that would be greatly appreciated. I just currently cannot think of a way I can logically create a query that would do this.

CodePudding user response：

So, you want all rows from table 1

And you want rows from table 2 that are A=16, B=0

And you want to know where the relationship breaks down between table1 and table2:

SELECT t1.* 
FROM
  table1 t1
  LEFT JOIN
  (
    SELECT * FROM table2 WHERE A=16 and B=0
  ) a16b0
  ON
    t1.id = a16b0.table1_id
WHERE
  a16b0.table1_id IS NULL

There are more ways to skin this cat, but this should be fairly understandable in the sense of "join table1 to (just the a16/b0 rows from table2)"

Another form you might get on with uses EXISTS:

SELECT * FROM table1 t1
WHERE NOT EXISTS(
  SELECT null FROM table2 t2 
  WHERE t2.table1_id = t1.id AND t2.A = 16 AND t2.B = 0
)

In english it's "Select all from table 1 where, for any particular t1 row, there does not exist a t2 row that has: a matching id in table1_id, a 16 in a, a 0 in b"

A slightly less popular form (historical performance reasons probably) would be perhaps:

SELECT * FROM table1 t1
WHERE id NOT IN (
  SELECT table1_id FROM table2 WHERE A = 16 AND B = 0
)

"select everything from table1 where the row's id is not in the list of IDs that are a16/b0 from table 2" - in essence this forms a "big list of everything we dont want" and then says "get me everything that isn't in the list of don't-wants"

CodePudding user response：

This is the solution.

SELECT 
    *
FROM
    table1
        LEFT JOIN
    table1.id = table2.table1_id AND table2.A = 16 AND table2.B = 0
WHERE 
    table2.id IS NULL;

@jon Armstrong, thanks for the help.