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    | grijesh.mnit@gmail.com    |
| 8        | Sumit      | sumit@cscape.in           |
| b        | OMD        | grijesh.chauhan@cscape.in |
+----------+------------+---------------------------+
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    | grijesh.mnit@gmail.com    |
| omdadmin  | OMD        | grijesh.chauhan@cscape.in |
+-----------+------------+---------------------------+
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.