MySQL Choice between ANY and EVERYTHING

Question

I have a mysql T1 table consisting of two INT columns that binds car_id to part_id. A single car_id may have several part_ids, and the same part_id may correspond to more than one car_id. For instance,

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 ran a 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) part_ids associated with car_id = 1. (In this example, I should get all car_ids that have at least 2 of 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 car_ids that contain All 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 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_id that have 2 or more cars 1 part_id 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 

Answer 2

Here is one way:

 select car_id from (select cp.car_id, sum(case when cp.part_id is not null and cp1.part_id is not null then 1 else 0 end) as PartsInCommon, sum(case when cp.part_id is not null and cp1.part_id is null then 1 else 0 end) as ThisCarOnly, sum(case when cp.part_id is null and cp1.part_id is not null then 1 else 0 end) as ThatCarOnly from CarParts cp full outer join (select part_id from CarParts cp where car_id = 1 ) cp1 on cp.part_id = cp1.part_id group by cp.car_id ) t where PartsInCommon / (PartsInCommon + ThisCarOnly + ThatCarOnly) >= 2.0/3 

This query counts the number of parts common to both cars, or in one or another. The where clause defines a specific condition.

If you need a list of parts, then Juergen has the right idea with group_concat() , although you won't mention this in the question.

Answer 3

Try this request. I tried as much as I can

 SELECT car_id, GROUP_CONCAT(part_id) FROM cars WHERE FIND_IN_SET (part_id ,(SELECT GROUP_CONCAT(part_id) FROM cars WHERE car_id = 1)) GROUP BY car_id HAVING COUNT(part_id) >= 2 

Here is the sqlfiddle daemon http://sqlfiddle.com/#!2/8e563/17