How to use left join with a subquery

Question

This or this questions do not answer my doubt.

I have the following set of tables in a database called order_mgmt_sys (Please note that database is static and I can't alter the tables)

mysql> show tables;
+--------------------------+
| Tables_in_order_mgmt_sys |
+--------------------------+
| customer_details |
| order_details |
| purchase_details |
+--------------------------+

---

 CREATE TABLE `customer_details` (
 `cust_id` bigint(11) NOT NULL AUTO_INCREMENT,
 `cust_name` varchar(50) NOT NULL,
 `cust_contact_1` bigint(11) NOT NULL,
 `cust_contact_2` bigint(11) DEFAULT NULL,
 `cust_email_id` varchar(60) DEFAULT NULL,
 `cust_address` varchar(200) NOT NULL,
 PRIMARY KEY (`cust_id`)
) 

---

 CREATE TABLE `purchase_details` (
 `pur_id` int(10) NOT NULL AUTO_INCREMENT,
 `order_id` int(10) NOT NULL,
 `item_type` varchar(20) NOT NULL,
 `qty` int(8) NOT NULL,
 `billing_amount` float NOT NULL,
 PRIMARY KEY (`pur_id`),
 KEY `FK_order_id` (`order_id`),
 CONSTRAINT `FK_order_id` 
 FOREIGN KEY (`order_id`) 
 REFERENCES `order_details` (`order_id`) 
 ON DELETE CASCADE ON UPDATE CASCADE
)

---

CREATE TABLE `order_details` (
 `order_id` int(10) NOT NULL AUTO_INCREMENT,
 `cust_id` bigint(11) NOT NULL,
 `delivery_address` varchar(200) NOT NULL,
 `delivery_date` datetime NOT NULL DEFAULT (_cp850'2019-08-12'),
 PRIMARY KEY (`order_id`),
 KEY `FK_cust_id` (`cust_id`),
 CONSTRAINT `FK_cust_id` 
 FOREIGN KEY (`cust_id`) 
 REFERENCES `customer_details` (`cust_id`)
)

I want the tables order_details and purchase_details left joined and should only give me the tuple which has the maximum billing_amount. (It should be noted here that I am completely new to SQL and don't know most of the concepts out there. So please do not judge my queries based on how silly they are or the inconvenience they cause. So please bear with my horrible queries😅)

This is how I proceeded:

query 1:

select max(billing_amount) from purchase_details;
+---------------------+
| max(billing_amount) |
+---------------------+
| 486753.03125 |
+---------------------+

query 2:

select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details);
+----------+
| order_id |
+----------+
| 27 |
+----------+

queries with issue:

expected result:
 +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+
 | order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount |
 +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+
 | 27 | 21 | Esker | 2010年11月29日 05:42:55 | 47 | 27 | House Decor | 77 | 486753 |
 +----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+
select * from order_details left outer join purchase_details on purchase_details.order_id=(select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details));
+----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+
| order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount |
+----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+
| 1 | 29 | Maywood | 2001年07月13日 12:49:20 | 47 | 27 | House Decor | 77 | 486753 |
| 4 | 37 | Hauk | 2015年07月03日 09:43:44 | 47 | 27 | House Decor | 77 | 486753 |
| 5 | 20 | Milwaukee | 2014年09月27日 23:28:28 | 47 | 27 | House Decor | 77 | 486753 |
| 7 | 46 | Westerfield | 2009年10月01日 16:02:50 | 47 | 27 | House Decor | 77 | 486753 |
| 8 | 43 | Bayside | 2019年02月07日 01:36:52 | 47 | 27 | House Decor | 77 | 486753 |
| 9 | 40 | Rieder | 2001年07月15日 22:34:30 | 47 | 27 | House Decor | 77 | 486753 |
| 11 | 9 | Canary | 2011年09月27日 19:05:42 | 47 | 27 | House Decor | 77 | 486753 |
| 12 | 34 | Transport | 2009年06月21日 01:45:12 | 47 | 27 | House Decor | 77 | 486753 |
| 14 | 3 | Prairieview | 2006年04月19日 00:05:57 | 47 | 27 | House Decor | 77 | 486753 |
| 15 | 14 | Gale | 2014年06月10日 05:39:01 | 47 | 27 | House Decor | 77 | 486753 |
| 16 | 5 | Porter | 2002年08月31日 18:49:55 | 47 | 27 | House Decor | 77 | 486753 |
| 17 | 45 | Michigan | 2011年06月15日 20:43:37 | 47 | 27 | House Decor | 77 | 486753 |
| 23 | 48 | Graedel | 2010年09月03日 08:27:04 | 47 | 27 | House Decor | 77 | 486753 |
| 24 | 22 | Bluestem | 2016年08月08日 01:37:35 | 47 | 27 | House Decor | 77 | 486753 |
| 26 | 24 | Barby | 2012年12月26日 16:18:03 | 47 | 27 | House Decor | 77 | 486753 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 47 | 27 | House Decor | 77 | 486753 |
| 32 | 19 | Cottonwood | 2009年01月06日 11:24:12 | 47 | 27 | House Decor | 77 | 486753 |
| 33 | 40 | Butternut | 2014年09月08日 08:42:06 | 47 | 27 | House Decor | 77 | 486753 |
| 34 | 5 | High Crossing | 2012年11月04日 06:29:45 | 47 | 27 | House Decor | 77 | 486753 |
| 35 | 42 | Stoughton | 2005年03月24日 14:46:11 | 47 | 27 | House Decor | 77 | 486753 |
| 38 | 17 | Hansons | 2007年09月12日 06:29:07 | 47 | 27 | House Decor | 77 | 486753 |
| 40 | 44 | Schiller | 2018年09月24日 07:25:53 | 47 | 27 | House Decor | 77 | 486753 |
| 41 | 1 | Evergreen | 2007年09月23日 05:43:36 | 47 | 27 | House Decor | 77 | 486753 |
| 42 | 5 | Lien | 2006年12月09日 02:23:17 | 47 | 27 | House Decor | 77 | 486753 |
| 43 | 16 | Sycamore | 2014年01月04日 13:17:22 | 47 | 27 | House Decor | 77 | 486753 |
| 44 | 39 | Dwight | 2001年12月28日 04:13:28 | 47 | 27 | House Decor | 77 | 486753 |
| 47 | 18 | Wayridge | 2018年11月10日 02:31:10 | 47 | 27 | House Decor | 77 | 486753 |
| 48 | 14 | High Crossing | 2009年03月20日 20:53:11 | 47 | 27 | House Decor | 77 | 486753 |
| 49 | 35 | Lighthouse Bay | 2013年11月07日 16:12:59 | 47 | 27 | House Decor | 77 | 486753 |
| 50 | 48 | Oriole | 2007年05月16日 22:17:49 | 47 | 27 | House Decor | 77 | 486753 |
+----------+---------+------------------+---------------------+--------+----------+-------------+------+----------------+
mysql> select * from order_details left outer join purchase_details on order_details.order_id=(select order_id from purchase_details where billing_amount=(select max(billing_amount) from purchase_details));
+----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+
| order_id | cust_id | delivery_address | delivery_date | pur_id | order_id | item_type | qty | billing_amount |
+----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 1 | 44 | Kitchware | 328 | 482370 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 2 | 50 | Electronics | 371 | 75544.5 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 3 | 24 | Furniture | 262 | 379261 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 4 | 49 | Stationery | 228 | 462698 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 5 | 32 | Electronics | 347 | 239346 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 6 | 12 | Bed-room Itenary | 154 | 363107 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 7 | 43 | Furniture | 111 | 409657 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 8 | 35 | House Decor | 231 | 102863 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 9 | 32 | Clothing | 106 | 463276 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 10 | 26 | Kitchware | 68 | 127504 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 11 | 5 | Bed-room Itenary | 57 | 159601 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 12 | 11 | Bed-room Itenary | 240 | 131163 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 13 | 38 | Electronics | 203 | 243343 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 14 | 47 | Kitchware | 338 | 44338.7 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 15 | 41 | Stationery | 427 | 50058.4 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 16 | 8 | Stationery | 441 | 256722 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 17 | 41 | Furniture | 78 | 425651 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 18 | 15 | House Decor | 477 | 182226 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 19 | 1 | Furniture | 118 | 328474 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 20 | 16 | Automobile | 219 | 474350 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 21 | 9 | Stationery | 252 | 457933 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 22 | 4 | Stationery | 433 | 471170 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 23 | 33 | Bed-room Itenary | 66 | 209335 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 24 | 17 | Automobile | 107 | 293932 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 25 | 48 | Stationery | 166 | 169077 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 26 | 7 | Kitchware | 357 | 393626 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 27 | 5 | Kitchware | 75 | 136595 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 28 | 49 | Stationery | 211 | 187452 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 29 | 15 | Automobile | 412 | 394621 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 30 | 41 | House Decor | 194 | 363274 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 31 | 14 | Furniture | 125 | 376607 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 32 | 23 | Stationery | 46 | 178141 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 33 | 48 | Automobile | 26 | 173840 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 34 | 33 | House Decor | 101 | 265796 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 35 | 48 | Electronics | 219 | 330784 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 36 | 5 | Automobile | 289 | 292930 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 37 | 9 | Bed-room Itenary | 79 | 28277.4 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 38 | 42 | Furniture | 241 | 155430 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 39 | 7 | Stationery | 159 | 150979 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 40 | 8 | Kitchware | 169 | 447978 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 41 | 35 | House Decor | 399 | 467603 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 42 | 14 | Bed-room Itenary | 484 | 305478 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 43 | 40 | Furniture | 396 | 133665 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 44 | 34 | Kitchware | 487 | 286046 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 45 | 24 | Electronics | 288 | 70668.5 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 46 | 26 | Clothing | 85 | 259455 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 47 | 27 | House Decor | 77 | 486753 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 48 | 47 | Stationery | 449 | 204334 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 49 | 15 | Furniture | 461 | 279387 |
| 27 | 21 | Esker | 2010年11月29日 05:42:55 | 50 | 26 | Stationery | 308 | 111537 |
| 1 | 29 | Maywood | 2001年07月13日 12:49:20 | NULL | NULL | NULL | NULL | NULL |
| 4 | 37 | Hauk | 2015年07月03日 09:43:44 | NULL | NULL | NULL | NULL | NULL |
| 5 | 20 | Milwaukee | 2014年09月27日 23:28:28 | NULL | NULL | NULL | NULL | NULL |
| 7 | 46 | Westerfield | 2009年10月01日 16:02:50 | NULL | NULL | NULL | NULL | NULL |
| 8 | 43 | Bayside | 2019年02月07日 01:36:52 | NULL | NULL | NULL | NULL | NULL |
| 9 | 40 | Rieder | 2001年07月15日 22:34:30 | NULL | NULL | NULL | NULL | NULL |
| 11 | 9 | Canary | 2011年09月27日 19:05:42 | NULL | NULL | NULL | NULL | NULL |
| 12 | 34 | Transport | 2009年06月21日 01:45:12 | NULL | NULL | NULL | NULL | NULL |
| 14 | 3 | Prairieview | 2006年04月19日 00:05:57 | NULL | NULL | NULL | NULL | NULL |
| 15 | 14 | Gale | 2014年06月10日 05:39:01 | NULL | NULL | NULL | NULL | NULL |
| 16 | 5 | Porter | 2002年08月31日 18:49:55 | NULL | NULL | NULL | NULL | NULL |
| 17 | 45 | Michigan | 2011年06月15日 20:43:37 | NULL | NULL | NULL | NULL | NULL |
| 23 | 48 | Graedel | 2010年09月03日 08:27:04 | NULL | NULL | NULL | NULL | NULL |
| 24 | 22 | Bluestem | 2016年08月08日 01:37:35 | NULL | NULL | NULL | NULL | NULL |
| 26 | 24 | Barby | 2012年12月26日 16:18:03 | NULL | NULL | NULL | NULL | NULL |
| 32 | 19 | Cottonwood | 2009年01月06日 11:24:12 | NULL | NULL | NULL | NULL | NULL |
| 33 | 40 | Butternut | 2014年09月08日 08:42:06 | NULL | NULL | NULL | NULL | NULL |
| 34 | 5 | High Crossing | 2012年11月04日 06:29:45 | NULL | NULL | NULL | NULL | NULL |
| 35 | 42 | Stoughton | 2005年03月24日 14:46:11 | NULL | NULL | NULL | NULL | NULL |
| 38 | 17 | Hansons | 2007年09月12日 06:29:07 | NULL | NULL | NULL | NULL | NULL |
| 40 | 44 | Schiller | 2018年09月24日 07:25:53 | NULL | NULL | NULL | NULL | NULL |
| 41 | 1 | Evergreen | 2007年09月23日 05:43:36 | NULL | NULL | NULL | NULL | NULL |
| 42 | 5 | Lien | 2006年12月09日 02:23:17 | NULL | NULL | NULL | NULL | NULL |
| 43 | 16 | Sycamore | 2014年01月04日 13:17:22 | NULL | NULL | NULL | NULL | NULL |
| 44 | 39 | Dwight | 2001年12月28日 04:13:28 | NULL | NULL | NULL | NULL | NULL |
| 47 | 18 | Wayridge | 2018年11月10日 02:31:10 | NULL | NULL | NULL | NULL | NULL |
| 48 | 14 | High Crossing | 2009年03月20日 20:53:11 | NULL | NULL | NULL | NULL | NULL |
| 49 | 35 | Lighthouse Bay | 2013年11月07日 16:12:59 | NULL | NULL | NULL | NULL | NULL |
| 50 | 48 | Oriole | 2007年05月16日 22:17:49 | NULL | NULL | NULL | NULL | NULL |
+----------+---------+------------------+---------------------+--------+----------+------------------+------+----------------+

Can anyone explain where am I going wrong?

• kesarling: I can't replace asterisk. I want all the field names
• kesarling: The task is to print the complete order_details joined with purchase_details of the guy with the maximum bill. Heck, it was to join all three!!! I still am stuck on the first two itself.
• Akina: One more question. If there is more than one order_id with the same (and maximal) billing_amount does you need in all of them, or one of them only specified by any additional conditions?
• kesarling: At least in my case, there is only one. I really have no idea, what to do in that case 😅. I guess the easier way out will do :)
• kesarling: Let's print them both. Do you find that okay or would you like to suggest any better alternative?

Answer 1

You query: I want the tables order_details and purchase_details left joined and should only give me the tuple which has the maximum billing_amount.

First of all, LEFT JOIN is normally used when you know that there is some information that might be in tableA but not in tableB for columnX.

In your case, if we think about your schema, if an order exists then its purchase details should exist also otherwise the data would be corrupted. So LEFT JOIN is out of the question. However, if you also wanted details of ALL the customers, then you could've done something like:

SELECT ... FROM customer_details AS cd LEFT JOIN order_details AS od ON cd.cust_id = od.cust_id LEFT JOIN purchase_details pd ON pd.order_id ON od.order_id

Hope that makes you understand about when to use LEFT JOIN.

You are approaching to the problem correctly but missing the concept. So a simple query would be like:

SELECT * FROM order_details AS od INNER JOIN purchase_details AS pd ON od.order_id = pd.order_id ORDER BY billing_amount DESC LIMIT 1;

Hope this helps your question :)

• zubair-0: AS clause basically gives an alias to the table name so you can easily reuse the alias afterwards. But you can choose not to use the AS clause but then you would have to use the table name with every column name afterwards. For Example: With AS clause we do: FROM order_details AS od INNER JOIN purchase_details AS od ON od.order_id = pd.order_id Without AS clause we would do: FROM order_details INNER JOIN purchase_details ON order_details.order_id = purchase_details.order_id
• zubair-0: If we divide the above query into parts: 1. select max(billing_amount) from purchase_details) is correct 2. SELECT pd.order_id FROM purchase_details WHERE ... is not correct (you are selecting the order_id of pd but you have not given an alias to purchase_details). If you want to use subqueries in this case then do not use join. I cannot explain in detail in comments section. Im sure if you take questions like these to your instructor, they can appreciate you learning and explain really well.
• zubair-0: Also for your satisfaction, highly inefficient and not recommended but it would work: 1. select * from order_details as od inner join purchase_details as pd on od.order_id=(select order_id from purchase_details where billing_amount = (select billing_amount from purchase_details ORDER BY billing_amount DESC LIMIT 1))

Answer 2

Test:

WITH cte AS ( SELECT order_id, 
 RANK() OVER (ORDER BY billing_amount DESC) rnk
 FROM purchase_details )
SELECT cd.*, od.*, pd.*
 FROM customer_details cd
 JOIN order_details od ON cd.cust_id = od.cust_id
 JOIN purchase_details pd ON od.order_id = pd.order_id
 JOIN cte ON od.order_id = cte.order_id
WHERE cte.rnk=1
/* ORDER BY cd.cust_id, od.order_id, pd.pur_id */

CTE enumerates all order_id by billing_amount decrease. Main query collects the data from all tables and filters the combined records for order_id values which matches the maximal value.

LEFT JOIN is excess in your case - FKs and the condition by the most right table prevents non-existent parts.

Answer 3

After a lot of research, this is what I found (Considering outer join has to be replaced with inner join):

the sub-query

(select * from purchase_details pd where pd.billing_amount=(select max(billing_amount) from purchase_details))

returns not a number, but a table. That means, the on clause is not comparing two values, but two tables. Instead we can use the sub-query as the second table to the inner join clause. So,

select * from order_details od inner join (select * from purchase_details pd where pd.billing_amount=(select max(billing_amount) from purchase_details)) pd on od.order_id=pd.order_id;

is the correct query to be fired. Corrections as most welcome :)