SQLQuery Help

Question

Hi! Can anyone help me with the query to get this information from the database on the URL bellow?

• Names of customers and the items they bought whose price is less than 600.
• City of the client and name of the article of all the orders that were made.
  -Customer name and total sum of the number of orders.

https://www.db-fiddle.com/f/qcWXTy3cpjfgDSzW8GdwjA/0

Answer 1

Hi @juannntrujillo , it looks you need 3 separate queries. Please refer to below queries:

1. Names of customers and the items they bought whose price is less than 600.
   select C.Name,C.LastName,C.City,
   I.name as Item, I.Price
   from Orders O
   inner join Clients C on O.ClientId = C.Id
   inner join Items I on O.Id = I.Id
   where I.Price < 600;
2. City of the client and name of the article of all the orders that were made.
   select C.Name,C.LastName,C.City,
   I.name as Item, I.Price
   from Orders O
   inner join Clients C on O.ClientId = C.Id
   inner join Items I on O.Id = I.Id;
3. Customer name and total sum of the number of orders. select C.Name,C.LastName,COUNT(I.name)
   from Orders O
   inner join Clients C on O.ClientId = C.Id
   inner join Items I on O.Id = I.Id
   GROUP BY C.Name,C.LastName,C.ID

Please let us know if this helps, or else if you are looking for any other query just mention and we can proceed further.

Answer 2

Sorry, this is the complete data. If any tag is wrong just let me know, i will remove it.

-- phpMyAdmin SQL Dump
-- version 4.9.4

-- https://www.phpmyadmin.net/

-- Host: localhost:3306
-- Generation Time: Oct 10, 2020 at 08:17 PM
-- Server version: 10.3.24-MariaDB-log-cll-lve
-- PHP Version: 7.3.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 */;

--
-- Database: padewpid_consultas

---

-- Table structure for table Clients

--
CREATE TABLE Clients (
id int(11) NOT NULL,
Name char(50) DEFAULT NULL,
LastName char(50) DEFAULT NULL,
City char(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table Clients

--
INSERT INTO Clients (id, Name, LastName, City) VALUES
(1, 'Courtnay', 'Slixby', 'Mongolia'),
(2, 'Hayden', 'Petrulis', 'Greece'),
(3, 'Opaline', 'Kelmere', 'Russia'),
(4, 'Colan', 'Barme', 'Ethiopia'),
(5, 'Stan', 'MacLaig', 'Indonesia'),
(6, 'Stafani', 'Uttridge', 'Nigeria'),
(7, 'Mike', 'Forsey', 'Russia'),
(8, 'Fons', 'Cuthill', 'France'),
(9, 'Jerome', 'Mynett', 'Indonesia'),
(10, 'Sibby', 'Balam', 'Mongolia'),
(11, 'Rivy', 'Pandie', 'Philippines'),
(12, 'Ruthy', 'Kemmis', 'Japan'),
(13, 'Zoe', 'Kolodziej', 'Colombia'),
(14, 'Tammara', 'Capstack', 'France'),
(15, 'Hermie', 'McKinie', 'China'),
(16, 'Jeni', 'Fishwick', 'Sweden'),
(17, 'Alissa', 'Annandale', 'Russia'),
(18, 'Magda', 'Dunmore', 'Croatia'),
(19, 'Winnie', 'Clemett', 'China'),
(20, 'Fionnula', 'MacKeeg', 'Armenia'),
(21, 'Brit', 'Diver', 'Kosovo'),
(22, 'Foss', 'Moran', 'China'),
(23, 'Bobette', 'Wharfe', 'China'),
(24, 'Marlena', 'Crottagh', 'Bulgaria'),
(25, 'Pearla', 'Underhill', 'Costa Rica'),
(26, 'Portie', 'Kidby', 'Indonesia'),
(27, 'Dorette', 'Silver', 'Benin'),
(28, 'Melosa', 'Cattle', 'Philippines'),
(29, 'Fawn', 'Berryann', 'Slovenia'),
(30, 'Buckie', 'Klink', 'China');

---

-- Table structure for table Items

--
CREATE TABLE Items (
id int(11) NOT NULL,
Name char(50) DEFAULT NULL,
Quantity char(50) DEFAULT NULL,
Price char(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table Items

--
INSERT INTO Items (id, Name, Quantity, Price) VALUES
(1, 'Chicken Breast Wing On', '69', '41'),
(2, 'Squid - Breaded', '59', '1737'),
(3, 'Rice - Basmati', '57', '591'),
(4, 'Carbonated Water - Strawberry', '99', '731'),
(5, 'Chicken - Bones', '72', '2743'),
(6, 'Bagel - 12 Grain Preslice', '22', '2155'),
(7, 'Oil - Sunflower', '92', '1072'),
(8, 'Lamb Rack - Ontario', '31', '135'),
(9, 'Turkey - Breast, Smoked', '47', '2197'),
(10, 'Nougat - Paste / Cream', '87', '258'),
(11, 'Pepper - Paprika, Spanish', '53', '1389'),
(12, 'Chicken - Livers', '25', '1875'),
(13, 'Rabbit - Whole', '81', '664'),
(14, 'Rappini - Andy Boy', '32', '1986'),
(15, 'Wine - Pinot Noir Stoneleigh', '96', '39'),
(16, 'Juice - Apple, 500 Ml', '68', '299'),
(17, 'Beer - Molson Excel', '35', '1350'),
(18, 'Nori Sea Weed', '7', '958'),
(19, 'Ecolab - Mikroklene 4/4 L', '31', '1771'),
(20, 'Pate - Peppercorn', '50', '654'),
(21, 'Wine - Jafflin Bourgongone', '46', '2518'),
(22, 'Bread - Sticks, Thin, Plain', '42', '2102'),
(23, 'Syrup - Monin, Swiss Choclate', '54', '2945'),
(24, 'Beer - Sleemans Cream Ale', '3', '801'),
(25, 'Bagelers - Cinn / Brown Sugar', '16', '296'),
(26, 'Appetizer - Tarragon Chicken', '16', '363'),
(27, 'Milk - Condensed', '30', '467'),
(28, 'Pants Custom Dry Clean', '97', '2237'),
(29, 'Kaffir Lime Leaves', '98', '2806'),
(30, 'Crackers - Water', '24', '700');

---

-- Table structure for table Orders

--
CREATE TABLE Orders (
id int(11) NOT NULL,
ClientID char(50) DEFAULT NULL,
ItemID char(50) DEFAULT NULL,
Quantity char(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table Orders

--
INSERT INTO Orders (id, ClientID, ItemID, Quantity) VALUES
(1, '5', '29', '88'),
(2, '12', '21', '69'),
(3, '10', '5', '52'),
(4, '26', '14', '14'),
(5, '9', '12', '34'),
(6, '24', '2', '18'),
(7, '13', '11', '31'),
(8, '11', '30', '44'),
(9, '26', '22', '37'),
(10, '18', '14', '93'),
(11, '10', '30', '16'),
(12, '22', '16', '48'),
(13, '24', '6', '64'),
(14, '16', '9', '77'),
(15, '27', '15', '66'),
(16, '9', '17', '39'),
(17, '4', '26', '54'),
(18, '3', '29', '82'),
(19, '4', '16', '43'),
(20, '17', '24', '62'),
(21, '21', '4', '69'),
(22, '14', '12', '89'),
(23, '21', '30', '59'),
(24, '15', '1', '40'),
(25, '9', '27', '63'),
(26, '20', '2', '43'),
(27, '25', '11', '87'),
(28, '14', '13', '50'),
(29, '14', '24', '17'),
(30, '23', '25', '94');

--
-- Indexes for dumped tables

---

-- Indexes for table Clients

ALTER TABLE Clients
ADD PRIMARY KEY (id);

--

-- Indexes for table Items

ALTER TABLE Items
ADD PRIMARY KEY (id);

--

-- Indexes for table Orders

ALTER TABLE Orders
ADD PRIMARY KEY (id),
ADD KEY IDCliente (ClientID),
ADD KEY IDArticulo (ItemID);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;