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?

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. — kesarling, Commented Aug 15, 2019 at 11:30
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? — Akina, Commented Aug 15, 2019 at 11:40
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, Commented Aug 15, 2019 at 11:41
Let's print them both. Do you find that okay or would you like to suggest any better alternative? — kesarling, Commented Aug 15, 2019 at 11:45

zubair-0 · Accepted Answer · 2019-08-16 12:08:33Z

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

edited Aug 16, 2019 at 12:08

answered Aug 15, 2019 at 14:23

zubair-0

1114 bronze badges

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
Commented Aug 15, 2019 at 15:53
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
Commented Aug 16, 2019 at 12:42
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));
– zubair-0
Commented Aug 16, 2019 at 12:45

Add a comment |

Akina · Accepted Answer · 2019-08-15 12:04:54Z

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.

kesarling · Accepted Answer · 2019-08-18 05:47:37Z

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

Stack Exchange Network

How to use left join with a subquery

3 Answers 3

Your Answer

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

Linked

Hot Network Questions

How to use left join with a subquery

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

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