MySQL: Sub-queries: How can I make this work?

Question

I have this assignment and I suppose I'm absolutely baffled as far as the instructions go, but I'm building a subquery. I'm certain I've done something ridiculous but I'm not sure how to go about fixing it. Does anyone really understand these instructions?

Instructions:

Use sub-queries to determine which artist had an item ordered in highest quantity on an order? First determine the highest quantity on any order, then determine the item id that is associated with it, then display the artist.

What I have so far:

SELECT 
       SUM(order_details.order_qty) as 'Order Quantity',
       items.item_ID as 'Item ID',
       artists.artist_name as 'Artist'
FROM orders
JOIN order_details on order_details.order_ID = orders.order_ID
JOIN items on items.item_ID = order_details.item_ID
JOIN artists on items.artist_id = artists.artist_id
WHERE SUM(order_details.order_qty) >
        (SELECT --How can I set this So that the quantity is being compared...this is where I'm stuck.)
GROUP BY items.item_id DESC;

How do you go about deciding to use a subquery. It's being used in a conditional clause...but it's for comparative reasons, yes?

ALL CODE:

/*Drop Tables*/
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS employees;

/*Create Tables*/
CREATE TABLE customers
(
 customer_id      INT,
 customer_first_name  VARCHAR(20),
 customer_last_name  VARCHAR(20)   NOT NULL,
 customer_address   VARCHAR(50)  NOT NULL,
 customer_city     VARCHAR(20)   NOT NULL,
 customer_state    CHAR(2)     NOT NULL,
 customer_zip     CHAR(5)   NOT NULL,
 customer_phone    CHAR(10)   NOT NULL,
 customer_fax     CHAR(10),
 CONSTRAINT customers_pk 
 PRIMARY KEY (customer_id)
);

CREATE TABLE artists
(
 artist_id      INT       NOT NULL,
 artist_name  VARCHAR(30),
 CONSTRAINT artist_pk 
 PRIMARY KEY (artist_id)
);

CREATE TABLE items
(
 item_id    INT       NOT NULL,
 title     VARCHAR(50)   NOT NULL,
 artist_id   INT   NOT NULL,
 unit_price  DECIMAL(9,2)  NOT NULL,
 CONSTRAINT items_pk 
  PRIMARY KEY (item_id),
 CONSTRAINT items_fk_artists
  FOREIGN KEY (artist_id) REFERENCES artists (artist_id)
);

CREATE TABLE employees
(
 employee_id     INT        NOT NULL,
 last_name      VARCHAR(20)    NOT NULL,
 first_name     VARCHAR(20)    NOT NULL,
  manager_id     INT,
CONSTRAINT employees_pk 
 PRIMARY KEY (employee_id),
CONSTRAINT emp_fk_mgr FOREIGN KEY (manager_id) REFERENCES employees(employee_id) 
);

CREATE TABLE orders
(
 order_id     INT     NOT NULL,
 customer_id    INT     NOT NULL,
 order_date    DATE    NOT NULL,
 shipped_date   DATE,
 employee_id    INT,
 CONSTRAINT orders_pk
  PRIMARY KEY (order_id),
 CONSTRAINT orders_fk_customers
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
CONSTRAINT orders_fk_employees
  FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

CREATE TABLE order_details
(
 order_id   INT      NOT NULL,
 item_id    INT      NOT NULL,
 order_qty   INT      NOT NULL,
 CONSTRAINT order_details_pk 
  PRIMARY KEY (order_id, item_id),
 CONSTRAINT order_details_fk_orders
  FOREIGN KEY (order_id)
  REFERENCES orders (order_id),
 CONSTRAINT order_details_fk_items
  FOREIGN KEY (item_id)
  REFERENCES items (item_id)
);

/*Insert Statements*/
INSERT INTO customers VALUES 
(1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928'),
(2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262'),
(3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800',NULL),
(4,'Mikayla','Davis','2021 K Street Nw','Washington','DC','20006','2025555561',NULL),
(5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043',NULL),
(6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826'),
(7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130',NULL),
(8,'Deborah','Davis','415 E Olive Ave','Fresno','CA','93728','5595558060',NULL),
(9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000',NULL),
(10,'Kurt','Nickalus','28210N Avenue Stanford','Valencia','CA','91355','8055550584','055556689'),
(11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302'),
(12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772',NULL),
(13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245',NULL),
(14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742',NULL),
(15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000',NULL),
(16,'Dakota','Baylee','1033 NSycamore Ave.','Los Angeles','CA','90038','2135554322',NULL),
(17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434',NULL),
(18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037',NULL),
(19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900',NULL),
(20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500',NULL),
(21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580'),
(22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495'),
(23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732',NULL),
(24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700',NULL),
(25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115',NULL);

INSERT INTO artists(artist_id,artist_name) VALUES 
(10,'Umani'),
(11,'The Ubernerds'),
(12,'No Rest For The Weary'),
(13,'Burt Ruggles'),
(14,'Sewed the Vest Pocket'),
(15,'Jess & Odie'),
(16,'Onn & Onn');

INSERT INTO items (item_id,title,artist_id,unit_price) VALUES 
(1,'Umami In Concert',10,17.95),
(2,'Race Car Sounds',11,13),
(3,'No Rest For The Weary',12,16.95),
(4,'More Songs About Structures and Comestibles',12,17.95),
(5,'On The Road With Burt Ruggles',13,17.5),
(6,'No Fixed Address',14,16.95),
(7,'Rude Noises',15,13),
(8,'Burt Ruggles: An Intimate Portrait',13,17.95),
(9,'Zone Out With Umami',10,16.95),
(10,'Etcetera',16,17);

INSERT INTO employees VALUES 
(1,'Smith','Cindy',null),
(2,'Jones','Elmer',1),
(3,'Simonian','Ralph',2),
(9,'Locario','Paulo',1),
(8,'Leary','Rhea',9),
(4,'Hernandez','Olivia',9),
(5,'Aaronsen','Robert',4),
(6,'Watson','Denise',8),
(7,'Hardy','Thomas',2);

INSERT INTO orders VALUES
(19,1,'2012-10-23','2012-10-28',6),
(29,8,'2012-11-05','2012-11-11',6),
(32,11,'2012-11-10','2012-11-13',NULL),
(45,2,'2012-11-25','2012-11-30',NULL),
(70,10,'2012-12-28','2013-01-07',5),
(89,22,'2013-01-20','2013-01-22',7),
(97,20,'2013-01-29','2013-02-02',5),
(118,3,'2013-02-24','2013-02-28',7),
(144,17,'2013-03-21','2013-03-29',NULL),
(158,9,'2013-04-04','2013-04-20',NULL),
(165,14,'2013-04-11','2013-04-13',NULL),
(180,24,'2013-04-25','2013-05-30',NULL),
(231,15,'2013-06-14','2013-06-22',NULL),
(242,23,'2013-06-24','2013-07-06',3),
(264,9,'2013-07-15','2013-07-18',6),
(298,18,'2013-08-18','2013-09-22',3),
(321,2,'2013-09-09','2013-10-05',6),
(381,7,'2013-11-08','2013-11-16',7),
(413,17,'2013-12-05','2014-01-11',7),
(442,5,'2013-12-28','2014-01-03',5),
(479,1,'2014-01-30','2014-03-03',3),
(491,16,'2014-02-08','2014-02-14',5),
(523,3,'2014-03-07','2014-03-15',3),
(548,2,'2014-03-22','2014-04-18',NULL),
(550,17,'2014-03-23','2014-04-03',NULL),
(601,16,'2014-04-21','2014-04-27',NULL),
(607,20,'2014-04-25','2014-05-04',NULL),
(624,2,'2014-05-04','2014-05-09',NULL),
(627,17,'2014-05-05','2014-05-10',NULL),
(630,20,'2014-05-08','2014-05-18',7),
(651,12,'2014-05-19','2014-06-02',7),
(658,12,'2014-05-23','2014-06-02',7),
(687,17,'2014-06-05','2014-06-08',NULL),
(693,9,'2014-06-07','2014-06-19',NULL),
(703,19,'2014-06-12','2014-06-19',7),
(778,13,'2014-07-12','2014-07-21',7),
(796,17,'2014-07-19','2014-07-26',5),
(800,19,'2014-07-21','2014-07-28',NULL),
(802,2,'2014-07-21','2014-07-31',NULL),
(824,1,'2014-08-01',NULL,NULL),
(827,18,'2014-08-02',NULL,NULL),
(829,9,'2014-08-02',NULL,NULL);

INSERT INTO order_details VALUES 
(381,1,1),
(601,9,1),
(442,1,1),
(523,9,1),
(630,5,1),
(778,1,1),
(693,10,1),
(118,1,1),
(264,7,1),
(607,10,1),
(624,7,1),
(658,1,1),
(800,5,1),
(158,3,1),
(321,10,1),
(687,6,1),
(827,6,1),
(144,3,1),
(479,1,2),
(630,6,2),
(796,5,1),
(97,4,1),
(601,5,1),
(800,1,1),
(29,10,1),
(70,1,1),
(165,4,1),
(180,4,1),
(231,10,1),
(413,10,1),
(491,6,1),
(607,3,1),
(651,3,1),
(703,4,1),
(802,3,1),
(824,7,2),
(829,1,1),
(550,4,1),
(796,7,1),
(693,6,1),
(29,3,1),
(32,7,1),
(242,1,1),
(298,1,1),
(479,4,1),
(548,9,1),
(627,9,1),
(778,3,1),
(19,5,1),
(89,4,1),
(242,6,1),
(264,4,1),
(550,1,1),
(693,7,3),
(824,3,1),
(829,5,1),
(829,9,1);

Let me know if you can't read the text.

if you can add some images that show us all the columns for your tables, or maybe provide an sql fiddle that would help greatly ^^ — Jester, Apr 05 '16 at 08:59
I'm sorry, I tried to use SQL Fiddle, I can't seem to ever get anything to run. If you want the code, I can provide that as well, but I put up some pictures of the tables. — Sierra, Apr 05 '16 at 18:05
haha, it's pretty simple, i just made one using your exact code ^^ just leave out the drop tables, as there aren't any tables to drop yet cause you haven't created them http://sqlfiddle.com/#!9/248259 — Jester, Apr 05 '16 at 18:17
? i'm confused? we weren't talking about select statements here? what are you trying to ask? :) — Jester, Apr 05 '16 at 18:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/108318/discussion-between-sierra-mcgivney-and-jester). — Sierra, Apr 05 '16 at 18:39

Jester · Accepted Answer · 2016-04-05T18:31:51.550

1

Okay, let's remake my answer, first of all this is what i think the easiest way to get the requested result:

SELECT SUM(order_details.order_qty) as 'Order Quantity',
   items.item_ID as 'Item ID',
   artists.artist_name as 'Artist'
FROM order_details
JOIN items on items.item_ID = order_details.item_ID
JOIN artists on items.artist_id = artists.artist_id
GROUP BY order_details.item_ID
ORDER BY SUM(order_details.order_qty) DESC
# LIMIT 1
# You can uncomment the line above if you only want to return the artist with the highest selling item

Query with subquery, according to my best interpretation of the instructions:

SELECT artists.artist_name as 'Artist'
FROM artists
JOIN items on items.artist_id = artists.artist_id
WHERE items.item_id =(
    SELECT order_details.item_ID as 'Item ID'
    FROM order_details
    GROUP BY order_details.item_id
    ORDER BY SUM(order_details.order_qty) DESC
    LIMIT 1);

In the inner query you select the item_id with the highest amount of sales from all the item id's. Then in the outer query you grab all the artists and their items and take the artist who owns the item_id that equals the result of the inner query.

(edit, i used the wrong way to comment MySQL in first query, fixed it)

edited Apr 05 '16 at 18:31

answered Apr 05 '16 at 09:08

Jester

1,408
1
9
21

Thank you for your help. I don't really understand the need for sub-queries. It seems that a person could typically do everything without them, but I'm not super experienced enough to say so. I provided some pictures for clarification. I'll play around with that to see if it works though. I will post the code so you can see it too, if you want to. Thanks again for all of your help! – Sierra Apr 05 '16 at 18:07
i used you're code to build the tables for myself, my query doesn't seem to work yet. i'm looking at what i did wrong, i'll update my answer in a few minutes ^^ – Jester Apr 05 '16 at 18:20
in the mean time you can also read this http://stackoverflow.com/questions/4799820/when-to-use-sql-sub-queries-versus-a-standard-join you can substitute sub queries with join, if it's not correlated. (not depending on a value from the query outside) – Jester Apr 05 '16 at 18:22
Thank you very much. The way you set that up kind of made the build of a sub-query much easier to understand. And thank you for the link, I'll go take a look at that now. :) Would you say that sub-queries are not really needed generally, or are there circumstances in which a sub-query would work better? – Sierra Apr 05 '16 at 18:36
Nevermind, the link appears to address that last question. – Sierra Apr 05 '16 at 18:37

MySQL: Sub-queries: How can I make this work?

1 Answers1