Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I am also not sure that whether its possible with Join –  Grijesh Chauhan Apr 16 '13 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 '13 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. –  Jon of All Trades Apr 17 '13 at 21:34
    
@JonofAllTrades Yes actually your are correct. Although its not my DB I will think about it. –  Grijesh Chauhan Apr 19 '13 at 15:32

2 Answers 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')
share|improve this answer
    
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 '13 at 22:21
    
+ Thanks for answer, actually 2nd JOIN condition is WHERE user_login`.Created_by = 'omdadmin'` that derives from grijesh row ... –  Grijesh Chauhan Apr 17 '13 at 17:52

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.

share|improve this answer
    
Thanks for the answer and it looks what I need I think! –  Grijesh Chauhan Apr 19 '13 at 15:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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