基于Another Query的值的不同查询的相交或公共值

I've a parent table like this

我有这样的父表

    ID    |   ParentTestID    |   TestID
----------------------------------------
    1     |        10         |    15
    2     |        10         |    25
    3     |        10         |    35

Second Table

ID    |   TestID    |   TestName    |   LanguageID
-----------------------------------------
1     |   15         |    Test1      |      8  
2     |   15         |    Test2      |      3  
3     |   15         |    Test3      |      1  
4     |   25         |    Test1      |      5  
5     |   25         |    Test2      |      3  
6     |   25         |    Test3      |      4  
7     |   35         |    Test1      |      3  
8     |   35         |    Test2      |      8  
9     |   35         |    Test3      |      9  

My scenario is to find the common language ID from the second table when I know the ParentTestID only on the first table

我的方案是当我只知道第一个表上的ParentTestID时,从第二个表中找到公共语言ID

That means if I know the parent TestID 10, the result will be 3 since all the testIDs under 10 having a common language ID 3

这意味着如果我知道父TestID 10,结果将是3,因为10岁以下的所有testID都具有公共语言ID 3

Also the number of testIDs under a parent ID is unpredictable. it wont be just 3.

父ID下的testID数量也是不可预测的。它不会只是3。

Am looking for query without cursor or such complications.

我正在寻找没有光标或类似复杂情况的查询。

2 个解决方案

#1

SELECT b.LanguageID
FROM    firstTable a
        INNER JOIN secondTable b    
            ON a.TestID = b.TestID
WHERE   a.ParentTestID = 10
GROUP   BY b.languageID
HAVING  COUNT(DISTINCT b.TestID) =
        (
          SELECT COUNT(c.testid) 
          FROM   firsttable c 
          WHERE  c.parenttestid = 10
        )

• SQLFiddle Demo

In the line HAVING COUNT(DISTINCT b.TestID), it is not needed to use DISTINCT keyword if the LanguageID is unique for every TestID.

在行HAVING COUNT(DISTINCT b.TestID)中,如果LanguageID对于每个TestID都是唯一的,则不需要使用DISTINCT关键字。

#2

Assuming there would be only 3 tests for each parenttestid, you may try the following query:

假设每个parenttestid只有3个测试,您可以尝试以下查询:

select distinct languageid
from child
where testid in (select testid from parent where parenttestid = 10)
group by languageid having count(*) > 2

Edit:

If the count is not fixed, then this query would work:

如果计数未修复,则此查询将起作用:

select distinct languageid
from child
where testid in (select testid from parent where parenttestid = 10)
group by languageid
having count(testid) = (select count(testid) from parent where parenttestid = 10)