MySQL: Join one Row in a table with two Rows in other table 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      |&lt;---|       | user_login_id         |
| first_name   |    |-------| user_id               |
| email_id     |            | user_name             |&lt;---|
+--------------+            | created_by            |----|
                            +-----------------------+



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 (∞)). 
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&gt; 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&gt; 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&gt; 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.
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.