Nested join, no value in one of the tables

Question

I have 4 tables Tab1 -- columns: id Tab2 -- columns: id, Tab1Id Tab3 -- columns: id, Tab2Id Tab4 -- columns: id, Tab3Id

There could be multiple rows of same id from another table. Eg: same Tab1Id can be present multiple times in Tab2, same Tab3Id can be present in Tab4 multiple times etc

I want to now get all Tab1 records, that dont have any rows in Tab4 through the connected tables.

I am able to get all the Tab3 that done have entry in Tab4, but then how do I put them in a group to know what entries from Tab1 have no entries in Tab4.

Tab1
+----+
| id |
+----+
| 1  |
+----+
| 2  |
+----+

Tab2
+----+--------+
| id | Tab1Id |
+----+--------+
| 10 | 1      |
+----+--------+
| 20 | 1      |
+----+--------+
| 30 | 2      |
+----+--------+
| 40 | 2      |
+----+--------+

Tab3
+----+--------+
| id | Tab2Id |
+----+--------+
| 100 | 10    |
+----+--------+
| 200 | 10    |
+----+--------+
| 300 | 20    |
+----+--------+

Tab4
+------+--------+
| id | Tab3Id |
+------+--------+
| 1000 | 100    |

So with my query I only want the id 2 from Tab1, as none of the corresponding entries for this id exist in Tab4

[SQL - find records from one table which don't exist in another](https://stackoverflow.com/q/367863/3404097) etc etc — philipxy, Jul 15 '22 at 00:02
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jul 15 '22 at 01:03

Ergest Basha · Answer 1 · 2022-07-15T07:16:00.777

0

One way:

SELECT t1.id 
FROM Tab1 t1 
WHERE id NOT IN ( SELECT t2.Tab1Id 
                  FROM Tab2 t2 
                  INNER JOIN Tab3 t3 on t3.Tab2Id=t2.id 
                  INNER JOIN Tab4 t4 on t4.Tab3Id=t3.id
                  );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=189d2e246b781f28e18dc5f93e847e20

The subquery will return the matching values in Tab2 ,Tab3 and Tab4 tables which will be filtered out by the NOT IN claues.

Another option would be using NOT EXISTS.

SELECT t1.id 
FROM Tab1 t1 
WHERE  NOT EXISTS ( SELECT 1  
                  FROM Tab2 t2 
                  INNER JOIN Tab3 t3 on t3.Tab2Id=t2.id 
                  INNER JOIN Tab4 t4 on t4.Tab3Id=t3.id
                  WHERE t2.Tab1Id=t1.id
                  )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6cde22a13c12c2c32f4860324ae0c92d

edited Jul 15 '22 at 07:16

answered Jul 14 '22 at 18:23

Ergest Basha

7,870
4
8
28

Thank you. But this doesnt solve my purpose as it shows all the records from Tab3 that dont have a record in Tab4. One record in Tab1 could have multiple records. Even if one of them have a record in Tab4, I dont want to show them in results. I have updated my question to have more clarity – Kay S Jul 15 '22 at 01:33
@KayS just changed `t2.id` with `t2.Tab1Id` and it gives your desired result, check the updated answer – Ergest Basha Jul 15 '22 at 07:10

Nested join, no value in one of the tables

1 Answers1