MySQL Selecting between ANY and ALL

Question

I have a mysql Table T1 consisting of two columns of INTs that links a car_id to a part_id. A single car_id can have multiple part_ids, and the same part_id can correspond to more than one car_id. For example,

car_id   part_id
  1        1  
  1        2  
  1        8  
  2        3  
  3        4  
  4        2  
  4        6  
     ...  
  10       1  
  10       2  
     ...  
  20       1  
  20       2  
  20       8  

To get all the part_ids associated with car_id = 1, I run the query,

SELECT car_id, part_id FROM T1 WHERE car_id=1

and get the result:

car_id   part_id
  1        1  
  1        2  
  1        8    

Now, I want to find all the remaining car_ids that contain at least (say >= 2/3) of the part_ids associated with car_id=1. (In this example, I should get all car_ids that have at least 2 of the part_ids 1,2, and 8 as shown after my SELECT query. So, I should get car_ids 1,10, and 20).

I can find the car_ids that contain All of the part_ids 1,2, and 8 using:

SELECT car_id, part_id 
      FROM T1 
      WHERE part_id = ALL (SELECT part_id FROM T1 WHERE car_id=1). The result is car_ids 1 and 20.  

I can find the car_ids that contain ANY of the values 1,2, and 8 using:

SELECT car_id, part_id 
       FROM T1 
       WHERE part_id = ANY (SELECT part_id FROM T1 WHERE car_id=1). The result is car_ids 1,4,10 and 20.

How can I specify some number between ANY and ALL?

Answer 1

To get all car_ids that have 2 or more of car 1's part_ids do

SELECT car_id, 
       group_concat(part_id) as part_ids
FROM T1
WHERE part_id in (SELECT part_id FROM T1 WHERE car_id = 1)
group by car_id
having count(distinct part_id) >= 2