Display the list of candidates' names-CodePudding

I have 2 tables:

candidates (candidat_id, first_name, name)
files (file_id, candidat_id, category, operation)

I want to display the list, sorted in alphabetical order of the names, of candidates who have more than one examination file with the same category and the same operation.

CodePudding user response：

if you group the files by candidat_id & category & operation, then the dubs will have a count above 1

SELECT candidat_id, category, operation
, COUNT(file_id) AS files
FROM files
GROUP BY candidat_id, category, operation
HAVING COUNT(file_id) > 1

If you group that by the candidate, then you can use it to get the names

SELECT first_name, name
FROM candidates
WHERE candidat_id IN (
  SELECT candidat_id
  FROM 
  (
     SELECT candidat_id, category, operation
     FROM files
     GROUP BY candidat_id, category, operation
     HAVING COUNT(file_id) > 1
  ) q
  GROUP BY candidat_id
)
ORDER BY first_name, name;

CodePudding user response：

you can do like this:

select f.candidat_id, f.file_id, f.category, f.operation, c.first_name, c.name -- to get file list with candidate or select c.name, fltr.cnt -- get candidate list with file_cnt
  from files f
  join candidates c
    on f.candidat_id = c.candidat_id
  join (
        select candidat_id, category, operation, count(*) cnt
          from files 
          group by 1,2,3
         having count(*) > 1
       )fltr
    on f.candidat_id = fltr.candidat_id 
   and f.category = fltr.category 
   and f.operation = fltr.operation 
 order by c.name asc

CodePudding user response：

One approach can be. First: Get the list of candidates has more than one operation file on same category, Then: Get the names of the list of candidates, and order by name this candidates list.

Here you can create a CTE (for easy read the query)

WITH operations ( candidat_id)
AS
    (
    SELECT Candidat_id
    From #files
    GROUP BY candidat_id, category, operation
    HAVING COUNT (candidat_id) >1
    )

SELECT
    candidates.first_name 
FROM
    operations as op INNER JOIN #candidates as candidates
        ON (op.candidat_id = candidates.candidat_id)
ORDER BY
    candidates.first_name

And other way can be, the same approacho but all togheter

SELECT 
     f.Candidat_id, c.first_name
From #files as f INNER JOIN #candidates AS c
        ON (f.candidat_id = c.candidat_id)
GROUP BY f.candidat_id, f.category, f.operation, c.first_name
HAVING COUNT (f.candidat_id) >1
ORDER BY c.first_name

In two solutions you use the pk fields to join, so the performance must be good

CodePudding user response：

You can find the candidates having more than one examination file with same category and operation, and then use EXISTS to find the candidate details.

Query

select t1.* from candidates as t1
where exists(
    select 1 from(
        select candidate_id, count(file_id) as files
        from files 
        group by candidate_id, category, operation
        having count(file_id) > 1
    ) as t2
    where t1.candidate_id = t2.candidate_id
)
order by first_name, name;