knysha knysha - 1 month ago 4
PHP Question

MySQL - inner join - add column with value based on other value

I'm struggling with mysql joins :/

I've multiple tables inside database fe. tasks, users etc.

Table tasks containing tasks with various variables, but the most important - id's of users signed to task (as different roles inside the task - author, graphic, corrector):

+---------+-------------+--------------+
| task_id | task_author | task_graphic |
+---------+-------------+--------------+
| 444 | 1 | 2 |
+---------+-------------+--------------+


Table users

+---------+----------------+------------+-----------+
| user_id | user_nice_name | user_login | user_role |
+---------+----------------+------------+-----------+
| 1 | Nice Name #1 | login1 | 0 |
+---------+----------------+------------+-----------+
| 2 | Bad Name #2 | login2 | 1 |
+---------+----------------+------------+-----------+


Using PDO I'm getting the whole data I want while using INNER JOIN with data from different tables (and $_GET variable)

SELECT tasks.*, types.types_name, warehouse.warehouse_id, warehouse.warehouse_code, warehouse.warehouse_description
FROM tasks
INNER JOIN types ON types.types_id = tasks.task_id
INNER JOIN warehouse ON warehouse.warehouse_id = tasks.task_id
WHERE tasks.task_id = '".$get_id."'
ORDER BY tasks.task_id


Above query returns:

+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+
| task_id | task_creator | task_graphic | task_purchaser | task_title | task_lang | task_description | task_description_files | task_files | task_status | task_prod_index | task_type | task_print_run | task_print_company | task_warehouse_code | task_cost | task_time_added | task_deadline | task_date_warehouse |
+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+
| 2 | 1 | 2 | 1 | Test | PL | Lorem ipsum (?) | | | w | 2222 | 3 | 456546 | Firma XYZ | 2 | 124 | 29.09.2016 15:48:20 | 01.10.2016 12:00 | 07.10.2016 14:00 |
+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+


And I'd like to get query with added user_nice_name after task_creator, task_author and task_graphic - obviously nice names selected from table users based on ID's provide in 3 above fields fe.

+---------+--------------+------------------------------------+--------------+--------------------------------------+
| task_id | task_creator | task_creator_nn | task_graphic | task_graphic |
+---------+--------------+------------------------------------+--------------+--------------------------------------+
| 2 | 1 | Nice Name (from task_creator ID=1) | 2 | Nice Name (from task_graphic ID = 2) |
+---------+--------------+------------------------------------+--------------+--------------------------------------+


How can I achieve that?

Answer

You need three joins:

SELECT t.*,
       uc.user_nice_name as creator_name,
       ug.user_nice_name as graphic_name,
       up.user_nice_name as purchaser_name,
       ty.types_name, w.warehouse_id, w.warehouse_code, w.warehouse_description
FROM tasks t INNER JOIN
     types ty
     ON ty.types_id = t.task_id INNER JOIN
     warehouse w
     ON w.warehouse_id = t.task_id LEFT JOIN
     users uc
     ON uc.user_id = t.task_creator LEFT JOIN
     users ug
     ON ug.user_id = t.task_graphic LEFT JOIN
     users up
     ON up.user_id = t.task_purchaser
WHERE t.task_id = '".$get_id."'
ORDER BY t.task_id;

Notes:

  • Table aliases make the query easier to write and to read. They are also required because you have three references to users in the FROM clause.
  • This uses LEFT JOIN for the users in case some of the reference values are missing.
  • You need to work on your naming. It doesn't make sense that a "warehouse" id matches a "task" id. Or that a "task" id matches a "types" id. But that is how you phrased the query in your question.
  • The ORDER BY effectively does nothing, because all rows have the same task_id.