# I'm a beginner and I have trouble with this SQL query

**URL:** <https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840>\
**Category:** SQL\
**Created:** [May 11, 2021, 8:52pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840 "2021-05-11T20:52:58Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 11, 2021, 8:52pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/1 "2021-05-11T20:52:59Z")

</div>

I’m using the tables from the course —sql\_store database—  
I just counting the amout of products (food) on the order\_items table.  
Works great!  
But I cannot put the name of the product (food) next to it it right.  
Let me share a picture.

I tried using JOIN but I can’t make it work

 ![sql-david](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/0/0550c55f76d782455cb7f4e6d181f3399c697385.png)

MY CODE

SELECT product\_id,  
COUNT(quantity) AS total  
FROM order\_items  
GROUP BY product\_id

RESULTS  
|1|3|  
|2|2|  
|3|4|  
|4|2|  
|5|2|  
|6|2|  
|8|1|  
|9|1|  
|10|1|

THANKS

---

<div class="post-metadata">

**Author:** ![JerryC](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/jerryc/32/859_2.png) [@JerryC](https://forum.codewithmosh.com/u/JerryC)\
**Post date:** [May 11, 2021, 10:13pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/2 "2021-05-11T22:13:22Z")

</div>

Hi David!

Try this:  
select p.product\_id, p.name, COUNT(i.quantity) AS total from products p, order\_items i where p.product\_id=i.product\_id

If that works, then add this to the end:  
GROUP BY i.product\_id

-Jerry

---

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 11, 2021, 11:01pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/3 "2021-05-11T23:01:23Z")

</div>

Hi Jerry,  
I tried that but don’t show the result I want.  
let me show you my attempt.

![result](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/5/59f461a84c3f28c241cdcb9d24f66bde7b2a19ad.jpeg)

---

<div class="post-metadata">

**Author:** ![JerryC](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/jerryc/32/859_2.png) [@JerryC](https://forum.codewithmosh.com/u/JerryC)\
**Post date:** [May 11, 2021, 11:37pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/4 "2021-05-11T23:37:05Z")

</div>

Sorry, that was my best guess without the tables and table data.

It looks like you are using a MySql Database schema.

Do you have the table create statements and insert statements for the two tables?  
If so, then I would be working with the same data as you.

I’ll be available until about 9:30 pm EST. Then probably not for a few days.  
If we don’t connect, then try:

select p.product\_id, p.name, i.quantity from products p, order\_items i where p.product\_id=i.product\_id

and work from there AND/OR ask for more advice.

---

<div class="post-metadata">

**Author:** ![JerryC](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/jerryc/32/859_2.png) [@JerryC](https://forum.codewithmosh.com/u/JerryC)\
**Post date:** [May 12, 2021, 1:53am UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/5 "2021-05-12T01:53:54Z")

</div>

Hey sorry, I missed you, David,

I believe that you are not a beginner and you are a master and looking for talent. LOL  
If not, then you are way ahead of structured SQL than most.

Continue your organization skills with other Computer Science technologies, and you will do well.

- Jerry

---

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 12, 2021, 12:16pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/6 "2021-05-12T12:16:10Z")

</div>

Hi Jerry!  
Im using the sql\_store squema from the course

![Capture123](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/3/3e858a7b6ed361a0f620b189da772583a8ce0921.jpeg)

below is the source code

---

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 12, 2021, 12:17pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/7 "2021-05-12T12:17:20Z")

</div>

DROP DATABASE IF EXISTS `sql_invoicing`;

CREATE DATABASE `sql_invoicing`;

USE `sql_invoicing`;

SET NAMES utf8 ;

SET character\_set\_client = utf8mb4 ;

CREATE TABLE `payment_methods` (

`payment_method_id` tinyint(4) NOT NULL AUTO\_INCREMENT,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`payment_method_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `payment_methods` VALUES (1,‘Credit Card’);

INSERT INTO `payment_methods` VALUES (2,‘Cash’);

INSERT INTO `payment_methods` VALUES (3,‘PayPal’);

INSERT INTO `payment_methods` VALUES (4,‘Wire Transfer’);

CREATE TABLE `clients` (

`client_id` int(11) NOT NULL,

`name` varchar(50) NOT NULL,

`address` varchar(50) NOT NULL,

`city` varchar(50) NOT NULL,

`state` char(2) NOT NULL,

`phone` varchar(50) DEFAULT NULL,

PRIMARY KEY (`client_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `clients` VALUES (1,‘Vinte’,‘3 Nevada Parkway’,‘Syracuse’,‘NY’,‘315-252-7305’);

INSERT INTO `clients` VALUES (2,‘Myworks’,‘34267 Glendale Parkway’,‘Huntington’,‘WV’,‘304-659-1170’);

INSERT INTO `clients` VALUES (3,‘Yadel’,‘096 Pawling Parkway’,‘San Francisco’,‘CA’,‘415-144-6037’);

INSERT INTO `clients` VALUES (4,‘Kwideo’,‘81674 Westerfield Circle’,‘Waco’,‘TX’,‘254-750-0784’);

INSERT INTO `clients` VALUES (5,‘Topiclounge’,‘0863 Farmco Road’,‘Portland’,‘OR’,‘971-888-9129’);

CREATE TABLE `invoices` (

`invoice_id` int(11) NOT NULL,

`number` varchar(50) NOT NULL,

`client_id` int(11) NOT NULL,

`invoice_total` decimal(9,2) NOT NULL,

`payment_total` decimal(9,2) NOT NULL DEFAULT ‘0.00’,

`invoice_date` date NOT NULL,

`due_date` date NOT NULL,

`payment_date` date DEFAULT NULL,

PRIMARY KEY (`invoice_id`),

KEY `FK_client_id` (`client_id`),

CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `invoices` VALUES (1,‘91-953-3396’,2,101.79,0.00,‘2019-03-09’,‘2019-03-29’,NULL);

INSERT INTO `invoices` VALUES (2,‘03-898-6735’,5,175.32,8.18,‘2019-06-11’,‘2019-07-01’,‘2019-02-12’);

INSERT INTO `invoices` VALUES (3,‘20-228-0335’,5,147.99,0.00,‘2019-07-31’,‘2019-08-20’,NULL);

INSERT INTO `invoices` VALUES (4,‘56-934-0748’,3,152.21,0.00,‘2019-03-08’,‘2019-03-28’,NULL);

INSERT INTO `invoices` VALUES (5,‘87-052-3121’,5,169.36,0.00,‘2019-07-18’,‘2019-08-07’,NULL);

INSERT INTO `invoices` VALUES (6,‘75-587-6626’,1,157.78,74.55,‘2019-01-29’,‘2019-02-18’,‘2019-01-03’);

INSERT INTO `invoices` VALUES (7,‘68-093-9863’,3,133.87,0.00,‘2019-09-04’,‘2019-09-24’,NULL);

INSERT INTO `invoices` VALUES (8,‘78-145-1093’,1,189.12,0.00,‘2019-05-20’,‘2019-06-09’,NULL);

INSERT INTO `invoices` VALUES (9,‘77-593-0081’,5,172.17,0.00,‘2019-07-09’,‘2019-07-29’,NULL);

INSERT INTO `invoices` VALUES (10,‘48-266-1517’,1,159.50,0.00,‘2019-06-30’,‘2019-07-20’,NULL);

INSERT INTO `invoices` VALUES (11,‘20-848-0181’,3,126.15,0.03,‘2019-01-07’,‘2019-01-27’,‘2019-01-11’);

INSERT INTO `invoices` VALUES (13,‘41-666-1035’,5,135.01,87.44,‘2019-06-25’,‘2019-07-15’,‘2019-01-26’);

INSERT INTO `invoices` VALUES (15,‘55-105-9605’,3,167.29,80.31,‘2019-11-25’,‘2019-12-15’,‘2019-01-15’);

INSERT INTO `invoices` VALUES (16,‘10-451-8824’,1,162.02,0.00,‘2019-03-30’,‘2019-04-19’,NULL);

INSERT INTO `invoices` VALUES (17,‘33-615-4694’,3,126.38,68.10,‘2019-07-30’,‘2019-08-19’,‘2019-01-15’);

INSERT INTO `invoices` VALUES (18,‘52-269-9803’,5,180.17,42.77,‘2019-05-23’,‘2019-06-12’,‘2019-01-08’);

INSERT INTO `invoices` VALUES (19,‘83-559-4105’,1,134.47,0.00,‘2019-11-23’,‘2019-12-13’,NULL);

CREATE TABLE `payments` (

`payment_id` int(11) NOT NULL AUTO\_INCREMENT,

`client_id` int(11) NOT NULL,

`invoice_id` int(11) NOT NULL,

`date` date NOT NULL,

`amount` decimal(9,2) NOT NULL,

`payment_method` tinyint(4) NOT NULL,

PRIMARY KEY (`payment_id`),

KEY `fk_client_id_idx` (`client_id`),

KEY `fk_invoice_id_idx` (`invoice_id`),

KEY `fk_payment_payment_method_idx` (`payment_method`),

CONSTRAINT `fk_payment_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_payment_payment_method` FOREIGN KEY (`payment_method`) REFERENCES `payment_methods` (`payment_method_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `payments` VALUES (1,5,2,‘2019-02-12’,8.18,1);

INSERT INTO `payments` VALUES (2,1,6,‘2019-01-03’,74.55,1);

INSERT INTO `payments` VALUES (3,3,11,‘2019-01-11’,0.03,1);

INSERT INTO `payments` VALUES (4,5,13,‘2019-01-26’,87.44,1);

INSERT INTO `payments` VALUES (5,3,15,‘2019-01-15’,80.31,1);

INSERT INTO `payments` VALUES (6,3,17,‘2019-01-15’,68.10,1);

INSERT INTO `payments` VALUES (7,5,18,‘2019-01-08’,32.77,1);

INSERT INTO `payments` VALUES (8,5,18,‘2019-01-08’,10.00,2);

DROP DATABASE IF EXISTS `sql_store`;

CREATE DATABASE `sql_store`;

USE `sql_store`;

CREATE TABLE `products` (

`product_id` int(11) NOT NULL AUTO\_INCREMENT,

`name` varchar(50) NOT NULL,

`quantity_in_stock` int(11) NOT NULL,

`unit_price` decimal(4,2) NOT NULL,

PRIMARY KEY (`product_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `products` VALUES (1,‘Foam Dinner Plate’,70,1.21);

INSERT INTO `products` VALUES (2,‘Pork - Bacon,back Peameal’,49,4.65);

INSERT INTO `products` VALUES (3,‘Lettuce - Romaine, Heart’,38,3.35);

INSERT INTO `products` VALUES (4,‘Brocolinni - Gaylan, Chinese’,90,4.53);

INSERT INTO `products` VALUES (5,‘Sauce - Ranch Dressing’,94,1.63);

INSERT INTO `products` VALUES (6,‘Petit Baguette’,14,2.39);

INSERT INTO `products` VALUES (7,‘Sweet Pea Sprouts’,98,3.29);

INSERT INTO `products` VALUES (8,‘Island Oasis - Raspberry’,26,0.74);

INSERT INTO `products` VALUES (9,‘Longan’,67,2.26);

INSERT INTO `products` VALUES (10,‘Broom - Push’,6,1.09);

CREATE TABLE `shippers` (

`shipper_id` smallint(6) NOT NULL AUTO\_INCREMENT,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`shipper_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `shippers` VALUES (1,‘Hettinger LLC’);

INSERT INTO `shippers` VALUES (2,‘Schinner-Predovic’);

INSERT INTO `shippers` VALUES (3,‘Satterfield LLC’);

INSERT INTO `shippers` VALUES (4,‘Mraz, Renner and Nolan’);

INSERT INTO `shippers` VALUES (5,‘Waters, Mayert and Prohaska’);

CREATE TABLE `customers` (

`customer_id` int(11) NOT NULL AUTO\_INCREMENT,

`first_name` varchar(50) NOT NULL,

`last_name` varchar(50) NOT NULL,

`birth_date` date DEFAULT NULL,

`phone` varchar(50) DEFAULT NULL,

`address` varchar(50) NOT NULL,

`city` varchar(50) NOT NULL,

`state` char(2) NOT NULL,

`points` int(11) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (`customer_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `customers` VALUES (1,‘Babara’,‘MacCaffrey’,‘1986-03-28’,‘781-932-9754’,‘0 Sage Terrace’,‘Waltham’,‘MA’,2273);

INSERT INTO `customers` VALUES (2,‘Ines’,‘Brushfield’,‘1986-04-13’,‘804-427-9456’,‘14187 Commercial Trail’,‘Hampton’,‘VA’,947);

INSERT INTO `customers` VALUES (3,‘Freddi’,‘Boagey’,‘1985-02-07’,‘719-724-7869’,‘251 Springs Junction’,‘Colorado Springs’,‘CO’,2967);

INSERT INTO `customers` VALUES (4,‘Ambur’,‘Roseburgh’,‘1974-04-14’,‘407-231-8017’,‘30 Arapahoe Terrace’,‘Orlando’,‘FL’,457);

INSERT INTO `customers` VALUES (5,‘Clemmie’,‘Betchley’,‘1973-11-07’,NULL,‘5 Spohn Circle’,‘Arlington’,‘TX’,3675);

INSERT INTO `customers` VALUES (6,‘Elka’,‘Twiddell’,‘1991-09-04’,‘312-480-8498’,‘7 Manley Drive’,‘Chicago’,‘IL’,3073);

INSERT INTO `customers` VALUES (7,‘Ilene’,‘Dowson’,‘1964-08-30’,‘615-641-4759’,‘50 Lillian Crossing’,‘Nashville’,‘TN’,1672);

INSERT INTO `customers` VALUES (8,‘Thacher’,‘Naseby’,‘1993-07-17’,‘941-527-3977’,‘538 Mosinee Center’,‘Sarasota’,‘FL’,205);

INSERT INTO `customers` VALUES (9,‘Romola’,‘Rumgay’,‘1992-05-23’,‘559-181-3744’,‘3520 Ohio Trail’,‘Visalia’,‘CA’,1486);

INSERT INTO `customers` VALUES (10,‘Levy’,‘Mynett’,‘1969-10-13’,‘404-246-3370’,‘68 Lawn Avenue’,‘Atlanta’,‘GA’,796);

CREATE TABLE `order_statuses` (

`order_status_id` tinyint(4) NOT NULL,

`name` varchar(50) NOT NULL,

PRIMARY KEY (`order_status_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `order_statuses` VALUES (1,‘Processed’);

INSERT INTO `order_statuses` VALUES (2,‘Shipped’);

INSERT INTO `order_statuses` VALUES (3,‘Delivered’);

CREATE TABLE `orders` (

`order_id` int(11) NOT NULL AUTO\_INCREMENT,

`customer_id` int(11) NOT NULL,

`order_date` date NOT NULL,

`status` tinyint(4) NOT NULL DEFAULT ‘1’,

`comments` varchar(2000) DEFAULT NULL,

`shipped_date` date DEFAULT NULL,

`shipper_id` smallint(6) DEFAULT NULL,

PRIMARY KEY (`order_id`),

KEY `fk_orders_customers_idx` (`customer_id`),

KEY `fk_orders_shippers_idx` (`shipper_id`),

KEY `fk_orders_order_statuses_idx` (`status`),

CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_orders_order_statuses` FOREIGN KEY (`status`) REFERENCES `order_statuses` (`order_status_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_orders_shippers` FOREIGN KEY (`shipper_id`) REFERENCES `shippers` (`shipper_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `orders` VALUES (1,6,‘2019-01-30’,1,NULL,NULL,NULL);

INSERT INTO `orders` VALUES (2,7,‘2018-08-02’,2,NULL,‘2018-08-03’,4);

INSERT INTO `orders` VALUES (3,8,‘2017-12-01’,1,NULL,NULL,NULL);

INSERT INTO `orders` VALUES (4,2,‘2017-01-22’,1,NULL,NULL,NULL);

INSERT INTO `orders` VALUES (5,5,‘2017-08-25’,2,’’,‘2017-08-26’,3);

INSERT INTO `orders` VALUES (6,10,‘2018-11-18’,1,‘Aliquam erat volutpat. In congue.’,NULL,NULL);

INSERT INTO `orders` VALUES (7,2,‘2018-09-22’,2,NULL,‘2018-09-23’,4);

INSERT INTO `orders` VALUES (8,5,‘2018-06-08’,1,‘Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.’,NULL,NULL);

INSERT INTO `orders` VALUES (9,10,‘2017-07-05’,2,‘Nulla mollis molestie lorem. Quisque ut erat.’,‘2017-07-06’,1);

INSERT INTO `orders` VALUES (10,6,‘2018-04-22’,2,NULL,‘2018-04-23’,2);

CREATE TABLE `order_items` (

`order_id` int(11) NOT NULL AUTO\_INCREMENT,

`product_id` int(11) NOT NULL,

`quantity` int(11) NOT NULL,

`unit_price` decimal(4,2) NOT NULL,

PRIMARY KEY (`order_id`,`product_id`),

KEY `fk_order_items_products_idx` (`product_id`),

CONSTRAINT `fk_order_items_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_order_items_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `order_items` VALUES (1,4,4,3.74);

INSERT INTO `order_items` VALUES (2,1,2,9.10);

INSERT INTO `order_items` VALUES (2,4,4,1.66);

INSERT INTO `order_items` VALUES (2,6,2,2.94);

INSERT INTO `order_items` VALUES (3,3,10,9.12);

INSERT INTO `order_items` VALUES (4,3,7,6.99);

INSERT INTO `order_items` VALUES (4,10,7,6.40);

INSERT INTO `order_items` VALUES (5,2,3,9.89);

INSERT INTO `order_items` VALUES (6,1,4,8.65);

INSERT INTO `order_items` VALUES (6,2,4,3.28);

INSERT INTO `order_items` VALUES (6,3,4,7.46);

INSERT INTO `order_items` VALUES (6,5,1,3.45);

INSERT INTO `order_items` VALUES (7,3,7,9.17);

INSERT INTO `order_items` VALUES (8,5,2,6.94);

INSERT INTO `order_items` VALUES (8,8,2,8.59);

INSERT INTO `order_items` VALUES (9,6,5,7.28);

INSERT INTO `order_items` VALUES (10,1,10,6.01);

INSERT INTO `order_items` VALUES (10,9,9,4.28);

CREATE TABLE `sql_store`.`order_item_notes` (

`note_id` INT NOT NULL,

`order_Id` INT NOT NULL,

`product_id` INT NOT NULL,

`note` VARCHAR(255) NOT NULL,

PRIMARY KEY (`note_id`));

INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES (‘1’, ‘1’, ‘2’, ‘first note’);

INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES (‘2’, ‘1’, ‘2’, ‘second note’);

DROP DATABASE IF EXISTS `sql_hr`;

CREATE DATABASE `sql_hr`;

USE `sql_hr`;

CREATE TABLE `offices` (

`office_id` int(11) NOT NULL,

`address` varchar(50) NOT NULL,

`city` varchar(50) NOT NULL,

`state` varchar(50) NOT NULL,

PRIMARY KEY (`office_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `offices` VALUES (1,‘03 Reinke Trail’,‘Cincinnati’,‘OH’);

INSERT INTO `offices` VALUES (2,‘5507 Becker Terrace’,‘New York City’,‘NY’);

INSERT INTO `offices` VALUES (3,‘54 Northland Court’,‘Richmond’,‘VA’);

INSERT INTO `offices` VALUES (4,‘08 South Crossing’,‘Cincinnati’,‘OH’);

INSERT INTO `offices` VALUES (5,‘553 Maple Drive’,‘Minneapolis’,‘MN’);

INSERT INTO `offices` VALUES (6,‘23 North Plaza’,‘Aurora’,‘CO’);

INSERT INTO `offices` VALUES (7,‘9658 Wayridge Court’,‘Boise’,‘ID’);

INSERT INTO `offices` VALUES (8,‘9 Grayhawk Trail’,‘New York City’,‘NY’);

INSERT INTO `offices` VALUES (9,‘16862 Westend Hill’,‘Knoxville’,‘TN’);

INSERT INTO `offices` VALUES (10,‘4 Bluestem Parkway’,‘Savannah’,‘GA’);

CREATE TABLE `employees` (

`employee_id` int(11) NOT NULL,

`first_name` varchar(50) NOT NULL,

`last_name` varchar(50) NOT NULL,

`job_title` varchar(50) NOT NULL,

`salary` int(11) NOT NULL,

`reports_to` int(11) DEFAULT NULL,

`office_id` int(11) NOT NULL,

PRIMARY KEY (`employee_id`),

KEY `fk_employees_offices_idx` (`office_id`),

KEY `fk_employees_employees_idx` (`reports_to`),

CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),

CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `employees` VALUES (37270,‘Yovonnda’,‘Magrannell’,‘Executive Secretary’,63996,NULL,10);

INSERT INTO `employees` VALUES (33391,‘D’arcy’,‘Nortunen’,‘Account Executive’,62871,37270,1);

INSERT INTO `employees` VALUES (37851,‘Sayer’,‘Matterson’,‘Statistician III’,98926,37270,1);

INSERT INTO `employees` VALUES (40448,‘Mindy’,‘Crissil’,‘Staff Scientist’,94860,37270,1);

INSERT INTO `employees` VALUES (56274,‘Keriann’,‘Alloisi’,‘VP Marketing’,110150,37270,1);

INSERT INTO `employees` VALUES (63196,‘Alaster’,‘Scutchin’,‘Assistant Professor’,32179,37270,2);

INSERT INTO `employees` VALUES (67009,‘North’,‘de Clerc’,‘VP Product Management’,114257,37270,2);

INSERT INTO `employees` VALUES (67370,‘Elladine’,‘Rising’,‘Social Worker’,96767,37270,2);

INSERT INTO `employees` VALUES (68249,‘Nisse’,‘Voysey’,‘Financial Advisor’,52832,37270,2);

INSERT INTO `employees` VALUES (72540,‘Guthrey’,‘Iacopetti’,‘Office Assistant I’,117690,37270,3);

INSERT INTO `employees` VALUES (72913,‘Kass’,‘Hefferan’,‘Computer Systems Analyst IV’,96401,37270,3);

INSERT INTO `employees` VALUES (75900,‘Virge’,‘Goodrum’,‘Information Systems Manager’,54578,37270,3);

INSERT INTO `employees` VALUES (76196,‘Mirilla’,‘Janowski’,‘Cost Accountant’,119241,37270,3);

INSERT INTO `employees` VALUES (80529,‘Lynde’,‘Aronson’,‘Junior Executive’,77182,37270,4);

INSERT INTO `employees` VALUES (80679,‘Mildrid’,‘Sokale’,‘Geologist II’,67987,37270,4);

INSERT INTO `employees` VALUES (84791,‘Hazel’,‘Tarbert’,‘General Manager’,93760,37270,4);

INSERT INTO `employees` VALUES (95213,‘Cole’,‘Kesterton’,‘Pharmacist’,86119,37270,4);

INSERT INTO `employees` VALUES (96513,‘Theresa’,‘Binney’,‘Food Chemist’,47354,37270,5);

INSERT INTO `employees` VALUES (98374,‘Estrellita’,‘Daleman’,‘Staff Accountant IV’,70187,37270,5);

INSERT INTO `employees` VALUES (115357,‘Ivy’,‘Fearey’,‘Structural Engineer’,92710,37270,5);

DROP DATABASE IF EXISTS `sql_inventory`;

CREATE DATABASE `sql_inventory`;

USE `sql_inventory`;

CREATE TABLE `products` (

`product_id` int(11) NOT NULL AUTO\_INCREMENT,

`name` varchar(50) NOT NULL,

`quantity_in_stock` int(11) NOT NULL,

`unit_price` decimal(4,2) NOT NULL,

PRIMARY KEY (`product_id`)

) ENGINE=InnoDB AUTO\_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci;

INSERT INTO `products` VALUES (1,‘Foam Dinner Plate’,70,1.21);

INSERT INTO `products` VALUES (2,‘Pork - Bacon,back Peameal’,49,4.65);

INSERT INTO `products` VALUES (3,‘Lettuce - Romaine, Heart’,38,3.35);

INSERT INTO `products` VALUES (4,‘Brocolinni - Gaylan, Chinese’,90,4.53);

INSERT INTO `products` VALUES (5,‘Sauce - Ranch Dressing’,94,1.63);

INSERT INTO `products` VALUES (6,‘Petit Baguette’,14,2.39);

INSERT INTO `products` VALUES (7,‘Sweet Pea Sprouts’,98,3.29);

INSERT INTO `products` VALUES (8,‘Island Oasis - Raspberry’,26,0.74);

INSERT INTO `products` VALUES (9,‘Longan’,67,2.26);

INSERT INTO `products` VALUES (10,‘Broom - Push’,6,1.09);

---

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 12, 2021, 7:12pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/8 "2021-05-12T19:12:31Z")

</div>

Jerry, my friend!

I GOT IT!

let me share with you my results.

![sql-davy](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/7/7c1df00ce749469bb05fc0c4abe267367ddad2fa.jpeg)

---

<div class="post-metadata">

**Author:** ![JerryC](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/jerryc/32/859_2.png) [@JerryC](https://forum.codewithmosh.com/u/JerryC)\
**Post date:** [May 12, 2021, 8:42pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/9 "2021-05-12T20:42:03Z")

</div>

David,  
Awesome! Great job!

See if the query still works if you change  
COUNT(quantity)  
COUNT(o.quantity) \<----- A reader will know what table “quantity” is in

-Jerry

---

<div class="post-metadata">

**Author:** ![waqarmirza.1996](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@waqarmirza.1996](https://forum.codewithmosh.com/u/waqarmirza.1996)\
**Post date:** [May 19, 2021, 5:23pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/10 "2021-05-19T17:23:57Z")

</div>

I have the same issue.

---

<div class="post-metadata">

**Author:** ![david\_ortiz](https://avatars.discourse-cdn.com/v4/letter/d/71e660/32.png) [@david\_ortiz](https://forum.codewithmosh.com/u/david_ortiz)\
**Post date:** [May 21, 2021, 4:33pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/11 "2021-05-21T16:33:24Z")

</div>

How can I help you?  
Im also learning

---

<div class="post-metadata">

**Author:** ![Naina19](https://avatars.discourse-cdn.com/v4/letter/n/c5a1d2/32.png) [@Naina19](https://forum.codewithmosh.com/u/Naina19)\
**Post date:** [December 28, 2021, 10:53am UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/12 "2021-12-28T10:53:57Z")

</div>

Hello all,  
If you are interested in learning SQL in deep then join the best [SQL Training](https://www.sevenmentor.com/sql-training-in-pune.php) which provides a deep understanding of SQL that will help you solve all the issues with SQL. However, with so many possibilities, finding platforms with quality instruction can be difficult.  
For your convenience, i would like to suggest this SQL Training available on the best online platforms for a seamless and enriching SQL learning experience.

---

<div class="post-metadata">

**Author:** ![reddy](https://avatars.discourse-cdn.com/v4/letter/r/f9ae1b/32.png) [@reddy](https://forum.codewithmosh.com/u/reddy)\
**Post date:** [January 4, 2022, 4:05am UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/14 "2022-01-04T04:05:38Z")

</div>

can you help me my problem is i wan to join all the tables is store database and get details of customer name, city , from order item(quantity and unitprice)he ordered,from order status(name), from orders(order\_date) , from products (name), from shipper(name) .all details from store table of one customer entire store database.  
i tried using left join but it is saying unidentified column

---

<div class="post-metadata">

**Author:** ![JerryC](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/jerryc/32/859_2.png) [@JerryC](https://forum.codewithmosh.com/u/JerryC)\
**Post date:** [January 4, 2022, 6:03pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/15 "2022-01-04T18:03:45Z")

</div>

Hi reddy,

Please do the following in MySQL Workbench:

1. Show me the output for “describe tableName;” for each tableName that you want to join.
2. Show me your latest join statement.

JerryC

---

<div class="post-metadata">

**Author:** ![reddy](https://avatars.discourse-cdn.com/v4/letter/r/f9ae1b/32.png) [@reddy](https://forum.codewithmosh.com/u/reddy)\
**Post date:** [January 31, 2022, 2:07pm UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/16 "2022-01-31T14:07:52Z")

</div>

SELECT o.order\_id,  
o.order\_date,  
concat (c.first\_name,’\_’, last\_name )as full\_name,  
[sh.name](http://sh.name) as shipper,  
[os.name](http://os.name) as status  
FROM ORDERS O  
JOIN CUSTOMERS C on o.customer\_id=c.customer\_id  
JOIN shippers sh on o.shipper\_id=sh.shipper\_id  
JOIN order\_statuses os on o.status=os.order\_status\_id  
JOIN order\_items.product\_id=product.product\_id

 ![image_2022-01-31_193702.png](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/c/c0e5ff6e13a4db841327d3ff4248720a5aee1da8.png)

 ![result.png](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/5/5a058fe38660a49e56a2a27f8052b044095a4f62.png)

---

<div class="post-metadata">

**Author:** ![reddy](https://avatars.discourse-cdn.com/v4/letter/r/f9ae1b/32.png) [@reddy](https://forum.codewithmosh.com/u/reddy)\
**Post date:** [February 1, 2022, 6:20am UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/17 "2022-02-01T06:20:03Z")

</div>

sir solution should be like. I WANTANTED TO JOIN ALL THE 5 TABLES IN STORE DATABASE  
the above image is order\_items table image, it has order\_id,product\_id as primary key.  
where my last join statement [order\_items.product\_id = products.product\_id] getting an error while executing .

---

<div class="post-metadata">

**Author:** ![reddy](https://avatars.discourse-cdn.com/v4/letter/r/f9ae1b/32.png) [@reddy](https://forum.codewithmosh.com/u/reddy)\
**Post date:** [February 3, 2022, 3:27am UTC](https://forum.codewithmosh.com/t/im-a-beginner-and-i-have-trouble-with-this-sql-query/4840/19 "2022-02-03T03:27:50Z")

</div>

![customer](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/0/01533a6ceece9751f1a0b9f76ae4737bd66420e0.png)  
 ![orders](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/3/323aa1fe61b1c70e58b276bab5815a1f07d4c8c2.png)  
 ![od_item](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/6/64d98a5eb48842400f8710e3a227ecd4d8478283.png)  
 ![products](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/5/51f977228a303d9d90cf41f7c6f821ab2e6c8678.png)  
 ![shipper](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/5/5124097ae6ce147c62943b4c076998e2ab122c36.png)  
 ![od_stat](https://us1.discourse-cdn.com/flex020/uploads/codewithmosh/original/2X/3/3085457f2a36322d9010528da97e4002b382e071.png)

want to join all this tables please give me the code for this im getting error ii have already shared my screen shot in community