MySQL: Join one Row in a table with two Rows in other table

Question

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):

  `users`                          `user_login`
+--------------+            +-----------------------+
| column_name  |            | column_name           |
+--------------+            +-----------------------+
| user_id      |<---|       | user_login_id         |
| first_name   |    |-------| user_id               |
| email_id     |            | user_name             |<---|
+--------------+            | created_by            |----|
                            +-----------------------+

1. Table: user_login with recursive association, such that an employee's username can be created by his boss in some web-based application . A self relationship of something like (created_by (1)- user_name (∞)).

2. Table: users contains personal information for each user(*or say for each username in user_login table*)

All fields are varchar(64) in both tables. And user_name is has uniqueness constraint. (the actual database I am working with is enough big with more columns and many rows, but I am just putting only useful informations)

[Query]

Input to my query is user_name.

I need a query If I give user_name value it return me information from user table (fist_name and email_id) for both user and his immediate boss.

Suppose my user table is:

mysql> SELECT `user_id`, `first_name`, `email_id` FROM `users`;
+----------+------------+---------------------------+
| user_id  | first_name | email_id                  |
+----------+------------+---------------------------+
| 1        | Grijesh    | [email protected]    |
| 8        | Sumit      | [email protected]           |
| b        | OMD        | [email protected] |
+----------+------------+---------------------------+
3 rows in set (0.00 sec)

And user_login is

mysql> SELECT user_login_id, user_id,  user_name , created_by FROM `user_login`;
+----------------+-----------+-----------+------------+
| user_login_id  | user_id   | user_name | created_by |
+----------------+-----------+-----------+------------+
| 13             | 1         | grijesh   | omdadmin   |
| 89             | 8         | sumit01   | grijesh    |
| bd             | b         | omdadmin  | SuperAdmin |
+----------------+-----------+-----------+------------+
3 rows in set (0.00 sec)

Then for the input user_name = 'grijesh' output details should be from user table about omd and grijesh.

For this I written a query like below (that is working fine):

mysql> SELECT  `user_login`.`user_name`, 
               `users`.`first_name`, 
               `users`.`email_id` 
       FROM    `user_login`, `users` 
       WHERE   `user_login`.`user_id` = `users`.`user_id` AND
                (`user_login`.`user_name` = 'grijesh' 
                  OR 
                 `user_login`.`user_name` IN ( SELECT `created_by` 
                                               FROM `user_login`   
                                              WHERE `user_login`.`user_name` = 'grijesh' ));

its output is like:

+-----------+------------+---------------------------+
| user_name | first_name | email_id                  |
+-----------+------------+---------------------------+
| grijesh   | Grijesh    | [email protected]    |
| omdadmin  | OMD        | [email protected] |
+-----------+------------+---------------------------+
2 rows in set (0.06 sec)                              

[QUESTION]:

Can we have better efficient query equivalent of my above. I tried to think a solution with Joins but its hard to me for this query because for a row in user_login I need to join with two rows in user table.

I tried with join but I couldn't. I need a solution with Join instead nested query like I did.

Any help or suggestion will be great help.

Answer 1

Perhaps I'm misunderstanding your request, but based on the text in your question that sounds simple enough:

SELECT
    U.first_name AS "User Name",
    U.email_id AS "User E-Mail Address",
    BossUser.first_name AS "Authorizing User Name"
    BossUser.email_id AS "Authorizing User E-Mail Address"
FROM
    user_login AS UL
    INNER JOIN users AS U ON UL.user_id = U.user_id
    LEFT JOIN  user_login AS BossLogin ON U.created_by = BossLogin.user_name
    LEFT JOIN  users AS BossUser ON BossLogin.user_id = BossUser.user_id

This allows for users without a created_by; if that field is NOT NULL, you can replace the LEFT JOINs with INNER JOIN.

Answer 2

try a UNION statement

SELECT  `user_login`.`user_name`, 
               `users`.`first_name`, 
               `users`.`email_id` 
FROM    `user_login`OIN `users` 
    ON   `user_login`.`user_id` = `users`.`user_id`
WHERE `user_login`.`user_name` = 'grijesh' 
UNION
SELECT  `user_login`.`user_name`, 
               `users`.`first_name`, 
               `users`.`email_id` 
FROM    `user_login`
JOIN `users` 
    ON   `user_login`.`user_id` = `users`.`user_id`
WHERE `user_login`.`user_login` in (SELECT  `Created_by`
FROM    `user_login`
WHERE `user_login`.`user_name` = 'grijesh')