Edited this question to add more details:
Table examples used:
table 1: names
| ID | names |
|---|---|
| 1 | sue |
| 2 | john |
table 2: training
| ID | type |
|---|---|
| 1 | winetasting |
| 2 | beerdrinking |
| 3 | whiskeyshots |
| 4 | shooters |
table 3: log
Note: result is just yes = 1 or no = 0 (complete/not completed)
| ID | nameid | typeid | result |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 0 |
| 3 | 2 | 3 | 1 |
Question: I want to return all students with training they have NOT YET COMPLETED. So, for instance, I am looking at the log table, and want to extract nameid, and then all typeids NOT taken yet. So, it’s comparing typeid field of log table to table 2 of list of training types – and returning training NOT COMPLETED.
| ID | nameid | typeid |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 4 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 4 | 2 | 4 |
OR THE text results
| ID | nameid | typeid |
|---|---|---|
| 1 | sue | beerdrinking |
| 2 | sue | whiskeyshots |
| 3 | sue | shooters |
| 4 | john | winetasting |
| 5 | john | beertasking |
| 6 | john | shooters |
So, results would show that Sue needs to complete training 2, 3 and 4. And John would have to complete 1, 2 and 4.
It’s almost like a NOT EXISTS or NOT IN. I have tried most joins. I am almost thinking this is a FOR EACH nameid, loop through table 3 and find all records from table 2 not in table 3 based on nameid and typeid, and build a temporary table. Maybe union?
Any resource links you can point out that might be similar to this, please let me know, or suggestions.
Thank you.
4
Looks like a simple cross-join of the two main tables, with a NOT EXISTS anti-join against the log table.
SELECT
n.ID,
n.names,
t.type
FROM names n
CROSS JOIN training t
WHERE NOT EXISTS (SELECT 1
FROM log l
WHERE l.nameid = n.id
AND l.typeid = t.id
AND l.result = 1
);
db<>fiddle
1