Need help building SQL query for single table

I have a table with the following fields:

ID 
brandID
productID

I want to know how many distinct brandIDs have at least one record in this table with both productID 1 and productID 2. For example:

ID|brandID|productID
1 | 1     | 1
2 | 1     | 2
3 | 1     | 2
4 | 2     | 2
5 | 3     | 1
6 | 3     | 1

In the above sample table, only brandID 1 has a row with productID 1 and productID 2. brandIDs 2 and 3 only have one product or the other.

How do I build a query to get this count of distinct brandIDs that contain both productID 1 and 2?

select COUNT(*)
from (
    select brandID
    from MyTable
    where productID in (1, 2)
    group by brandID
    having count(distinct productID) = 2
) a

If your table is named t, it should be something like this:

select count(distinct t1.brandID)
from t t1, t t2
where t1.brandID = t2.brandID and t1.productID = 1 and t2.productID = 2

Use a sub-select to get distinct brandID with productID=1, and then get the count:

SELECT COUNT(DISTINCT(brandID)) FROM tbl WHERE brandID IN (SELECT DISTINCT brandID FROM tbl WHERE productID=1) AND productID=2;

select distinct brandid, count(1) from sampletable 
 where productid in (1, 2) 
 group by brandid having count(1) > 1

for SQL Server 2005 and up:

DECLARE @t TABLE(ID INT, brandID INT, productID INT)
INSERT INTO @t
SELECT 1, 1, 1 UNION ALL 
SELECT 2, 1, 2 UNION ALL 
SELECT 3, 1, 2 UNION ALL 
SELECT 4, 2, 2 UNION ALL 
SELECT 5, 3, 1 UNION ALL 
SELECT 6, 3, 2 UNION ALL 
SELECT 7, 3, 4 UNION ALL 
SELECT 8, 3, 5

SELECT * FROM @t    

;WITH cte
AS
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY brandID ORDER BY productID) AS RN, *
    FROM    @t
    WHERE   productID IN (1,2)
)
SELECT  DISTINCT brandID
FROM    cte
WHERE   RN > 1

This is what I would do:

select distinct brandid from t
where brandid in (select distinct brandid from t where productid = 1)
and brandid in  (select distinct brandid from t where productid = 2)