CODEWITHSUNDEEP
mysql
Avag Sargsyan
Avag Sargsyan

Reputation: 2523

How to find all rows that have all values which has another row

My database contains students and marks tables, which you can see visiting http://www.sqlfiddle.com/#!2/817367/1. Query should return all studends whose got all marks which has gotten student 'c' (1, 2, 3), that is - b, e, f.

I have solved problem partly getting all students.name which has any of marks.score where students.name = 'c', but I can't figure out how to force to check all marks.score-es of 'c'.

...Hopefully question is clear.

Thanks in advance.

Upvotes: 2

Views: 87

Answers (3)

Rimas
Rimas

Reputation: 6024

Another similiar solution:

SELECT s2.name
FROM marks m1
JOIN marks m2 ON m2.score = m1.score
JOIN students s1 ON s1.id = m1.s_id
JOIN students s2 ON s2.id = m2.s_id
WHERE s1.name = 'c' AND s2.name != 'c'
GROUP BY s2.id
HAVING COUNT(DISTINCT m1.score)
 = (SELECT COUNT(DISTINCT m.score)
    FROM marks m
    JOIN students s ON s.id = m.s_id
    WHERE s.name = 'c')

Upvotes: 1

Hovo
Hovo

Reputation: 790

the other solutions are good for this time, but if you want a solution without using aggregate functions like "COUNT" then consider this. http://www.sqlfiddle.com/#!2/817367/39

SELECT `name`
FROM students
JOIN (SELECT DISTINCT s_id
    FROM marks AS marks1
    WHERE marks1.`s_id` NOT IN(
        SELECT DISTINCT marks2.s_id
        FROM (SELECT score
            FROM marks
            JOIN students ON marks.`s_id` = students.`id` 
                          AND students.`name` = 'c') AS c_scores
        CROSS JOIN marks AS marks2 
                           ON marks2.`s_id` NOT IN (
                            SELECT s_id
                            FROM marks
                            JOIN students ON marks.`s_id` = students.`id` 
                                          AND students.`name` = 'c')
        LEFT JOIN marks AS marks3 ON marks3.`s_id` = marks2.s_id 
                                  AND marks3.`score` = c_scores.score
        WHERE marks3.`s_id` IS NULL
    )) AS good_ids
ON students.`id` = good_ids.s_id
WHERE `name` != 'c'

Upvotes: 1

Jacky Cheng
Jacky Cheng

Reputation: 1556

got it ...phew, your requirements are weird as hell haha but this should give you what you need with a single input 'c'

maybe there are room to improve but at this point you're on your own.

somehow i can't update the fiddle so here it is

select t1.name 
from
  (select s1.name,count(*) as count 
     from marks m1 
     inner join marks m2 using (score)
     inner join students s1 on (s1.id=m2.s_id)
     inner join students s2 on (m1.s_id=s2.id)
     where s2.name='c'
     group by m2.s_id
   ) as t1
JOIN 
   (select count(*) as min_count 
     from marks m 
     inner join students s on (m.s_id=s.id)
     where s.name='c'
   ) as t2
where 
   t1.name != 'c' 
   and t1.count >= t2.min_count

Upvotes: 0

Related Questions