Mysql IF conditional Left Join same table twice

I have a "Tasks" table for work. There are 2 types of users in the system "Admin" and "Agent" both are stored in different tables called "Admin" and "Agents".

**Table Admin**
admin_id           |  1
admin_name         |  John
admin_surname      |  Doe

**Table Agents**
agent_id           |  1
agents_name        |  Sally
agents_surname     |  Zoe

And there is "Tasks" table where users can add task/requests for other users.

**Table Tasks**
task_id            | 1
task_inquirer_type | (1 if Admin, 2 if Agent)
task_inquirer_id   | ID from related user table
task_assigned_type | (1 if Admin, 2 if Agent)
task_assigned_id   | ID from related user table
task_text          | text

I'm trying to get all records in one Mysql Query with joining Admin and Agent tables to get names and surnames of inquirers and assigned users.

With the below SQL i can get the records for one user.

SELECT tasks.*,
       admins.admin_name as inquirer_name, 
       admins.admin_surname as inquirer_surname
FROM tasks
INNER JOIN admins on admins.admin_id = tasks.task_inquirer_id
WHERE tasks.task_inquirer_type = 1 
  AND task_assigned_id = 1
  AND task_assigned_type = 1
        
UNION
        
SELECT tasks.*,
       agents.agent_name as inquirer_name, 
       agents.agent_surname as inquirer_surname
FROM tasks
INNER JOIN agents on agents.agent_id = tasks.task_inquirer_id
WHERE tasks.task_inquirer_type = 2 
  AND task_assigned_id = 1
  AND task_assigned_type = 1

With this query, i can get all records assigned to Admin (1) user with ID (1). But i have made another page where i would like to list all records and get all names as "inquirer" and "assigned" in the list. I've tried the query below but somehow it gave me wrong names but the rest is ok.

SELECT tasks.*,
       inquirer.admin_name as inquirername,
       inquirer.admin_surname as inquirersurname,
       assigned.admin_name as assignedname,
       assigned.admin_surname as assignedsurname
FROM tasks

INNER JOIN admins AS inquirer on inquirer.admin_id = tasks.task_inquirer_id
INNER JOIN admins AS assigned on assigned.admin_id = tasks.task_assigned_id   
WHERE tasks.task_inquirer_type = 1
    
UNION
    
SELECT tasks.*,
       inquirer.agent_name as inquirername, 
       inquirer.agent_surname as inquirersurname,
       assigned.agent_name as assignedname,
       assigned.agent_surname as assignedsurname
FROM tasks
    
INNER JOIN agents AS inquirer on inquirer.agent_id = tasks.task_inquirer_id
INNER JOIN agents AS assigned on assigned.agent_id = tasks.task_assigned_id
    
WHERE tasks.task_inquirer_type = 2

Any help how should i process the query? I know that I've to look for both types, not only 1 type as WHERE tasks.task_inquirer_type = 1 or 2. There should be WHERE tasks.task_assigned_type = 1 or 2 too in the query which i can not figure out how to.

Solution

In my opinion you should change your data model. As is, you cannot guarantee data consistency. You could put any number into task_inquirer_id and task_assigned_id; the DBMS cannot help you controlling that an agent ID really exists in the agent table for instance.

If the tables admin and agent are very similar as in your example, make it one person table, with a flag showing whether the person is an agent or an admin. You'd change the tasks table as follows:

task_id (PK)
task_inquirer_person_id (FK not nullable)
task_assigned_person_id (FK not nullable)

with foreign keys to the person table. Whether an admin or agent will be implicit by the records linked.

If the tables admin and agent differ very much, then only change your tasks table like this:

task_id (PK)
task_inquirer_admin_id (FK nullable)
task_inquirer_agent_id (FK nullable)
task_assigned_admin_id (FK nullable)
task_assigned_agent_id (FK nullable)

Add foreign keys to the main tables as shown and add constraints to ensure that always only one inquirer and one assigned person is set in a record.

Querying the table will be much simpler then.