Suppose I have a table named users consist of columns: user_id, user_name, user_created_by.
+------------------+----------------------+-------------------+
| user_id + user_name + user_created_by +
+------------------+----------------------+-------------------+
| 1 | John | 1 |
| 2 | Ann | 1 |
| 3 | Paul | 2 |
| 4 | King | 2 |
| 5 | Dirk | 3 |
+------------------+----------------------+-------------------+
The value of user_created_by is the user_id who created that record. Now, I want to make a query that results one specific row with added column let's say user_created_by_name which is the user_name of the user_id from the user_created_by. Suppose we want to get "Paul"'s record with who (the name) create it (temporary new column). For ease of understanding this is my expected result:
+----------+--------------+-------------------+------------------------+
| user_id | user_name | user_created_by | user_created_by_name |
+----------+--------------+-------------------+------------------------+
| 3 | Paul | 2 | Ann |
+----------+--------------+-------------------+------------------------+
this is my query using codeigniter:
$query=$this->db->query("SELECT *,
(SELECT user_name FROM users WHERE user_id = user_created_by)
AS "user_created_by_name" FROM users WHERE user_id=3);
But my result are:
+----------+--------------+-------------------+------------------------+
| user_id | user_name | user_created_by | user_created_by_name |
+----------+--------------+-------------------+------------------------+
| 3 | Paul | 2 | NULL |
+----------+--------------+-------------------+------------------------+
You culd use a self join (join the same table two time) using alias for fere to the tables as different sets of data
SELECT a.user_id, a.user_name, a.user_created_by, b.user_name as user_created_by_name
from users a
inner join user b on a.user_created_by = b.user_id
where a.user_id = 3
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments