Select query for retrieving common data

Model   Icolor

m1  i1

m1  i2

m1  i3

m2  i2

m2  i3

m2  i4

I am having a table like this, i need to select Icolor values which is co开发者_StackOverflow社区mmon to both model m1 and m2.

DECLARE @TABLE TABLE
(
    Model VARCHAR(30),
    LColour VARCHAR(30)
)

INSERT INTO @TABLE 
SELECT 'm1','i1' UNION
SELECT 'm1','i2' UNION
SELECT 'm1','i3' UNION
SELECT 'm2','i2' UNION
SELECT 'm2','i3' UNION
SELECT 'm2','i4' 

SELECT 
    LColour
FROM 
    @TABLE
GROUP BY 
    LColour
HAVING 
    COUNT(*) = (SELECT COUNT(DISTINCT Model) FROM @TABLE)

Edited to give all colours that are common to every model :)

I beleive it shuld be something like this:

SELECT tt.Icolor
FROM test_table tt
INNER JOIN test_table tt1 ON tt1.Icolor = tt.Icolor AND tt1.Model = 'm2'
WHERE tt.Model = 'm1'

UPDATED:

select color from 
  (select color, T2.num from test_table, 
     (select count(*) as num from (select distinct model from test_table) T) T2
group by color, T2.num
having count(*) = T2.num) T3

select lcolor from Model where m1 = x
union 
select lcolor from Model where m2 = y

x is your model m1 and y is your model m2 value or if it is a bit datatype then you've got a 1 there. union gets you the shared values from the results of both queries. I'm not sure if the syntax is correct (but I'm pretty sure it is). If not you can google it.