WPFUser WPFUser - 5 months ago 30
SQL Question

SQL : Having / exists / except - advanced query

I have a list of entities which all support one or more languages.
I have a parent entity which also has a list of required languages.
I would like to know which of the languages if any required by the parent item that all of the children does not support.

ie:
Table1:

Lang_ID, ChildContent_ID
1 A
2 B
3 B
1 B
1 C
3 C
4 C
1 D
3 D
1 E


Table2:

ParentRequiredLang_IDs
1
2
3


In the above data we see that all children has language 1 support, but only B,C,D has language 3 support. Only B has language 2 support. The parent requires language 1,2,3 support, but is missing 2 and 3 in children. This is my desired output; 2,3.

Desired output:
2
3


How can I write this query?

Note: both of the tables here are the output of several joins, and should be dynamic. I cannot query on "2" and "3".

Answer Source

Here is one way to do it. You first create a CROSS JOIN of the content IDs and the required languages (to get all combinations that are required) and then you simply look for missing values and output the distinct language IDs of them.

SELECT
    DISTINCT ParentRequiredLang_IDs AS MissingLanguages
FROM
    (SELECT i.ChildContent_ID, j.ParentRequiredLang_IDs FROM Table_1 i CROSS JOIN Table_2 j) a
LEFT OUTER JOIN
    dbo.Table_1 b ON a.ChildContent_ID = b.ChildContent_ID AND a.ParentRequiredLang_IDs = b.Lang_ID
WHERE
    b.Lang_ID IS NULL