1

I have these two tables (simplified versions)

Orders

owner_id | user_1 | user_2 | amount | order_id
-----------------------------------------------
   1     |   2    |   3    |  100   |   AAA
   1     |   7    |   2    |  200   |   BBB
   2     |   3    |   5    |  400   |   CCC

Users

user_id | username
------------------
   1    |  John
   2    |  Robert
   3    |  Sally
   4    |  Mario
   5    |  Albert
   6    |  Hernest
   7    |  Homer

I need to get, in one query, all the info related to a particular order, including the owner_id, user_1, user_2 usernames.

The result I'm trying to achieve is the following:

owner_id | owner_username | user_1_id | user_1_username | user_2_id | user_2_username | order_total
----------------------------------------------------------------------------------------------------
   1     |     John       |    2      |     Robert      |    3      |     Sally       |     100

So far I'm getting all I need with a query like this:

SELECT o.owner_id AS owner_id, (SELECT username FROM Users where user_id = 1) AS owner_username,
       o.user_1 AS user_1_id, (SELECT username FROM Users where user_id = 2) AS user_1_username,
       o.user_2 AS user_2_id, (SELECT username FROM Users where user_id = 3) AS user_2_username,
       o.amount AS order_total
FROM Orders.o
WHERE o.order_id = 'AAA'

This is an example to retrieve the info for the first order.

I'm not very satisfied by the inner queries I have to do to get each username, I think it's kinda ugly.

Is there a more elegant or more performant way to get the usernames?

Thank you

BeNdErR
  • 17,471
  • 21
  • 72
  • 103

1 Answers1

3

This may help 

SELECT od.*,
       U1.username AS 'User_1_Name',
       U2.username AS 'User_2_Name',
       U3.username AS 'User_3_Name'
FROM   Orders od
       LEFT OUTER JOIN Users U1
                    ON od.Owner_Id = U1.User_Id
       LEFT OUTER JOIN Users U2
                    ON od.User_1 = U2.User_Id
       LEFT OUTER JOIN Users U3
                    ON od.User_2 = U3.User_Id
WHERE  order_id = 'AAA'
Deepak Pawar
  • 46
  • 1
  • Thanks, may I ask why this solution is better than mine? I'm trying to understand the advantages of using JOIN instead of directly use inner queries.. In my head the result is the same. – BeNdErR Oct 16 '14 at 10:25
  • As a general rule of thumb, try to avoid subqueries whenever possible. It's harder for the DBMS to optimize subqueries than regular joins. – wvdz Oct 16 '14 at 10:27
  • Query you wrote, will work only for the first record because you gave hardcoded where clause in you inner queries. Remove the where clause from your main query and run it then you will see that you are getting desired result only for first record. you need joins to get the corresponding value from other table. – Deepak Pawar Oct 16 '14 at 10:30