1

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.

4
  • I am also not sure that whether its possible with Join Apr 16, 2013 at 18:25
  • 1
    You did use a join you just used an implict join whch you shoudl stop using as they are SQL antipatterns
    – HLGEM
    Apr 16, 2013 at 22:13
  • 1
    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. Apr 17, 2013 at 21:34
  • @JonofAllTrades Yes actually your are correct. Although its not my DB I will think about it. Apr 19, 2013 at 15:32

2 Answers 2

2

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.

0
1

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')
2
  • 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.
    – HLGEM
    Apr 16, 2013 at 22:21
  • + Thanks for answer, actually 2nd JOIN condition is WHERE user_login.Created_by` = 'omdadmin'` that derives from grijesh row ... Apr 17, 2013 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.