Identify missing conversions with an SQL query

Question

I need to find a way to find what conversions are missing.

I have three tables.

Table 1: type, which are the different types.

id	name
1	typeA
2	typeB
3	typeC

Table 2: section, which are the different sections.

id	name
1	section1
2	section2
3	section3
4	section4

Table 3: conversions, which contains all the combinations to go from one type to another for each of the sections.

id	section_id	type_convert_from	type_convert_to
1	1	1	2
2	2	1	2
3	3	1	2
4	4	1	2
5	1	1	3
6	2	1	3
7	3	1	3
8	4	1	3
9	1	2	1
10	2	2	1
11	3	2	1
12	4	2	1

For example some are missing from the table above, how can I identify them with a SQL query? Thanks!

Use a `CROSS JOIN` to create all the possible combinations. Then filter out the combinations that are in the `conversions` table. — Barmar, Jul 02 '21 at 04:27
See https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to filter out the matching rows. — Barmar, Jul 02 '21 at 04:28
@bonnegnu . . . Some whats are missing? You have two type columns for instance. — Gordon Linoff, Jul 02 '21 at 12:28
@Gordon Linoff! Yes, it is to convert from one type to another in each section. Missing conversions need to be identified — bonnegnu, Jul 02 '21 at 12:57

John D · Accepted Answer · 2021-07-02T19:20:12.077

Try this. The cross join of table type with itself generates all possible combinations of type id's. I've excluded combinations in the cross join where id_from = id_to (ie you're not interested in conversions from a type to itself)

select * from conversions C 
    right join (
        select T1.id as id_from, T2.id as id_to 
        from type T1 cross join type T2
        where T1.id <> T2.id
        ) X on X.id_from = C.type_convert_from and X.id_to = C.type_convert_to
    where C.type_convert_from is null

If you want to check missing type conversions by section, extend the cross join by adding the section table to include section.id as follows. It will list missing type conversions within each section.

select X.section_id, X.id_from, X.id_to from conversions C 
right join (
    select S.id as section_id, T1.id as id_from, T2.id as id_to 
    from types T1 cross join types T2 cross join section S
     where T1.id <> T2.id
    ) X 
    on X.id_from = C.type_convert_from and X.id_to = C.type_convert_to
    and C.section_id = X.section_id
where C.type_convert_from is null

Hello @John D! Thanks for your help. Yes, I think this is the way. It only remains to add the condition that it checks if the combination exists for each of the sections. How would this be done? — bonnegnu, Jul 02 '21 at 11:33
@bonnegnu I've edited the answer to cater for checking missing conversions within each section. — John D, Jul 02 '21 at 19:23

Identify missing conversions with an SQL query

1 Answers1