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?
Best Answer
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_idHope 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 :)