Finding a set of value from one table in another table

[rubis]

Hi all,

I have two tables like below.

Table 1
word block
a 1
a 1
b 1
a 2
b 2
b 2
a 3
b 3
c 3

Table 2
word
a
b

I want to find the result that any block that contains a set of value in Table 2 appear together e.g. from the above, the result should be the block 1 and 2. If Table 2 contains a,b,c then the result should be the block 3. I'm wondering that it can be written in SQL. I tried to search for SQL command but I can't find any. Thanks.

Rubis

 

[tlbroadbent]

If I understand your requirement properly, this query should get the result you seek. Of course, the syntax of the query may or may not work in the database you use.

Select Block, count(*) As RecCnt
From (Select Block, Word From Table1
Group By Block, Word) As t1
Left Join Table2 As t2
On t1.word=t2.Word
Group By Block
Having count(t1.word)=(Select count(word) from Table2) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.