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.

• Grijesh Chauhan: I am also not sure that whether its possible with Join
• HLGEM: You did use a join you just used an implict join whch you shoudl stop using as they are SQL antipatterns
• Jon of All Trades: So a user can have multiple logins? From a design standpoint, created_by really should point to users.user_id, rather than a text field.
• Grijesh Chauhan: @JonofAllTrades Yes actually your are correct. Although its not my DB I will think about it.

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')

• HLGEM: If you would never have the case where the person is also the created by or if you would want two records in that case, then use UNION ALL as it tends to be faster.
• Grijesh Chauhan: + Thanks for answer, actually 2nd JOIN condition is WHERE user_login.Created_by` = 'omdadmin'` that derives from grijesh row ...