How to limit and search number of joined rows from table in multiple joins in mysql

Question

i have a following tables in MySQL database:

+------------------------+
|          Users         |
+----+--------+----------+
| id | name   | role     |
+----+--------+----------+
| 1  | Martin | admin    |
+----+--------+----------+
| 2  | George | admin    |
+----+--------+----------+
| 3  | John   | employee |
+----+--------+----------+

+-------------------------+
|          Forms          |
+----+--------------------+
| id | type               |
+----+--------------------+
| 10 | marketing_form     |
+----+--------------------+
| 11 | client_survey_form |
+----+--------------------+
| 12 | client_survey_form |
+----+--------------------+

+---------------------------------------------+
|              UsersAssignToForms             |
+----+---------+---------+--------------------+
| id | user_id | form_id | additional_comment |
+----+---------+---------+--------------------+
| 20 | 1       | 10      | Lorem ipsum...     |
+----+---------+---------+--------------------+
| 21 | 2       | 10      | Lorem ipsum....    |
+----+---------+---------+--------------------+
| 22 | 3       | 10      | null               |
+----+---------+---------+--------------------+
| 23 | 3       | 11      | null               |
+----+---------+---------+--------------------+

I would like to have result:

+---------+---------+------------+--------------------+--------------------+
| user_id | form_id | first_name | form_type          | additional_comment |
+---------+---------+------------+--------------------+--------------------+
| 1       | 10      | Martin     | marketing_form     | Lorem ipsum...     |
+---------+---------+------------+--------------------+--------------------+
| 3       | 11      | John       | client_survey_form | null               |
+---------+---------+------------+--------------------+--------------------+
| null    | 12      | null       | client_survey_form | null               |
+---------+---------+------------+--------------------+--------------------+

First of all i would like to limit number of users returned from join query (one user per one form). If user with admin role is assigned to form i would like to display this user (prioritize admin role over employee role) and limit number of returned users to 1, if admin is not assign, but employee is assigned query should return this user, if no-one is assign query should return nulls (left or right join probably).

I saw this question on stackoverflow - MySQL JOIN with LIMIT 1 on joined table, but unfortunately first answer has n+1 issue and rest of answers was made with simple one join. For my purposes i need to join more tables but wouldn't like to design this tables above to clarify what i would like to achieve, but it's very important.

So my query will looks like probably:

SELECT u.id, f.id, u.name, f.type, uf.additional_comment, [more selects from other tables...] FROM Forms as f
LEFT JOIN Users as u ON ......
INNER JOIN UsersAssignToForms as uf ON .....
[here i would like to put more and more inner joins.....]

Is there any logic for this row where null with different form id which is not existed in your given data? — Rahul Biswas, Sep 18 '21 at 07:11
Sry, i forget to modify Forms table with form that is assign to no-one. I've just updated — Marcin Warzybok, Sep 18 '21 at 09:56

nachospiu · Accepted Answer · 2021-09-18T22:07:58.393

In MySql >= 8.0 you can number the rows using some criteria (for each Form starting from one and order by u.role ASC and u.id ASC), then you can filter rows with number one:

WITH sq AS (SELECT u.id AS user_id, f.id AS form_id, u.name, f.type, uf.additional_comment,
                   ROW_NUMBER() OVER (PARTITION BY f.id ORDER BY u.role ASC, u.id ASC) AS num
       
            FROM Forms AS f
            LEFT JOIN UsersAssignToForms AS uf ON f.id = uf.form_id
            LEFT JOIN Users AS u ON u.id = uf.user_id)
            
SELECT *
FROM sq
WHERE num = 1;

Before MySql 8.0 you can try something like this (the idea is the same but with different implementation):

SELECT sq2.user_id, sq2.form_id, sq2.name, sq2.type, sq2.additional_comment
FROM (
    SELECT 
           sq1.*,
           
           @row_number:=CASE WHEN @form_id = sq1.form_id THEN @row_number + 1 ELSE 1 END AS num,
           @form_id:= sq1.form_id
        
    FROM (SELECT u.id AS user_id, f.id AS form_id, u.name, f.type, uf.additional_comment
          FROM Forms AS f
          LEFT JOIN UsersAssignToForms AS uf ON f.id = uf.form_id
          LEFT JOIN Users AS u ON u.id = uf.user_id
          ORDER BY f.id ASC, u.role ASC, u.id ASC) AS sq1
          
    ORDER BY sq1.form_id) AS sq2

WHERE sq2.num = 1;

I updated my answer with a query to MySql before 8.0 using the same idea. — nachospiu, Sep 18 '21 at 22:16

How to limit and search number of joined rows from table in multiple joins in mysql

1 Answers1