MySQL: Finding the most efficient use of INNER JOIN with subquery-CodePudding

I have a working query using INNER JOIN and a subquery but was wondering if there is a more effient way of writing it.

with prl
as
(
SELECT `number`, creator, notes
FROM ratings
INNER JOIN
    projects on ratings.project_id = projects.project_id
    WHERE ratings.rating = 5 AND projects.active = 1
)
SELECT prl.`number`, creator, notes
FROM prl
INNER JOIN(
    SELECT `number`
    HAVING COUNT(creator) > 1
)temp ON prl.`number` = temp.`number`
ORDER BY temp.`number`

projects table

project_id| number | creator | active |
|   1     |   3    |  bob    |    1   |
|   2     |   4    |  mary   |    1   |
|   3     |   5    |  asi    |    1   |

rating table

project_id|  notes | rating |
|   1     |  note1 |    5   |
|   1     |  note2 |    5   |
|   3     |  note3 |    5   |
|   1     |  note4 |    1   |
|   2     |  note5 |    5   |
|   3     |  note6 |    2   |

result

| number | creator |  notes |
|   3    |  bob    |  note1 |
|   3    |  bob    |  note2 |

CodePudding user response：

It seems like you're using MySQL version that support window function. If so, then try this:

SELECT number, creator, notes
FROM
(SELECT p.number, p.creator, r.notes,
       COUNT(creator) OVER (PARTITION BY creator) AS cnt
 FROM project p 
  JOIN rating r ON p.project_id=r.project_id
WHERE r.rating=5
   AND p.active = 1) v
WHERE cnt=2;

As far as whether this is more efficient, I'm not really sure because it depends in your table indexes but for a small dataset, I assume this will do well.

Demo fiddle