Question: I have a table which needs rows removing where two other columns match.
So for example:

```
Apples  |  Size   |  Colour  | Juiced
 1      |   big   |   red    |  Yes
 2      |   small |   green  |  No

```

I want the query to remove all rows that have size big, and colour red, and juiced.

the problem is I need to find out size and colour from the same table (so cannot use left join) but I require this table to be used in the IN statement.
I'm attempting to use IN to select rows like:

```
SELECT * from table WHERE (Juiced <> 'No') OR
(Juiced LIKE 'yes' AND (Size, Colour IN (SELECT Size, Colour FROM table WHERE blah=blah)));

```

SQL does not allow two variables to be matched within the table at once using IN, so is there any way I can get both Size and Colour to be recognised by the IN statement?

I'm using SQL Server 2008

Any help is greatly appreciated!

Answer: 
Conceptually - when you are thinking about **IN with multiple columns** your thinking about [EXISTS](http://technet.microsoft.com/enus/library/ms188336(v=sql.100).aspx):

```
SELECT * 
FROM table T
WHERE (Juiced <> 'No') OR (Juiced LIKE 'yes' AND 
                                            (EXISTS (SELECT Size, Colour 
                                                     FROM [table] A 
                                                     WHERE A.Apples = T.Apples AND
                                                           -- put your 'balhh here...')))

```
You could do something like this:

```
SELECT * FROM table
LEFT JOIN (
    SELECT Size, Colour
    FROM anotherTable
    WHERE condition
) anotherTable ON table.Size = anotherTable.Size AND table.Colour = anotherTable.Colour
WHERE (Juiced <> 'No') -- should this be (Juiced = 'No')?
OR (Juiced LIKE 'yes' AND anotherTable.Size IS NOT NULL);

```