2

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?

5
  • I can't replace asterisk. I want all the field names Aug 15 '19 at 11:25
  • 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. Aug 15 '19 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
    Aug 15 '19 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 :) Aug 15 '19 at 11:41
  • Let's print them both. Do you find that okay or would you like to suggest any better alternative? Aug 15 '19 at 11:45
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 :)

3
  • 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
    Aug 15 '19 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
    Aug 16 '19 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
    Aug 16 '19 at 12:45
0

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.

0
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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