How to use the result of a subquery

Question

I have three SQL tables as below:

(The "orders" table below is not complete)

How to resolve the following question using just one sql query:

Select the customers who ordered in 2014 all the products (at least) that the customers named 'Smith' ordered in 2013.

Is this possible?

I have thought about this:

1. Firstly, find the all the products that the client named "Smith" ordered in 2013.

2. Secondly, find the list of customers who at least have ordered all the above products in 2014.

Which brings me to a SQL query like this:

SELECT cname,
FROM customers
NATURAL JOIN orders
WHERE YEAR(odate) = '2014'
  AND "do_something_here"
    (SELECT DISTINCT pid
     FROM orders
     NATURAL JOIN customers
     WHERE LOWER(cname)='smith'
       AND YEAR(odate)='2013') as results;

in which all the subquery results (the list of products that "Smith" ordered in 2013) should be used to find the clients needed.

But I don't know if this is the good approach.

Sorry for my English because I am not a native speaker.

If you want to test it out on phpMyAdmin, here's the SQL:

-- phpMyAdmin SQL Dump
-- version 4.7.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Mar 21, 2018 at 02:49 PM
-- Server version: 5.7.20
-- PHP Version: 7.1.7

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: `tp1`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `cid` int(11) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `residence` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `cname`, `residence`) VALUES
(0, 'didnotorder', 'Great Britain'),
(1, 'Jones', 'USA'),
(2, 'Blake', NULL),
(3, 'Dupond', 'France'),
(4, 'Smith', 'Great Britain'),
(5, 'Gupta', 'India'),
(6, 'Smith', 'France');

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `odate` date NOT NULL,
  `quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`pid`, `cid`, `odate`, `quantity`) VALUES
(1, 1, '2014-12-12', 2),
(1, 4, '2014-11-12', 6),
(2, 1, '2014-06-02', 6),
(2, 1, '2014-08-20', 6),
(2, 1, '2014-12-12', 2),
(2, 2, '2010-11-12', 1),
(2, 2, '2014-07-21', 3),
(2, 3, '2014-10-01', 1),
(2, 3, '2014-11-12', 1),
(2, 4, '2014-01-07', 1),
(2, 4, '2014-02-22', 1),
(2, 4, '2014-03-19', 1),
(2, 4, '2014-04-07', 1),
(2, 4, '2014-05-22', 1),
(2, 4, '2014-09-12', 4),
(2, 6, '2014-10-01', 1),
(3, 1, '2014-12-12', 1),
(3, 2, '2013-01-01', 1),
(3, 4, '2015-10-12', 1),
(3, 4, '2015-11-12', 1),
(4, 1, '2014-12-12', 3),
(4, 2, '2014-06-11', 2),
(4, 5, '2014-10-12', 1),
(4, 5, '2014-11-13', 5),
(5, 2, '2015-07-21', 3),
(6, 2, '2015-07-21', 7),
(6, 3, '2014-12-25', 1);

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `pid` int(11) NOT NULL,
  `pname` varchar(30) NOT NULL,
  `price` decimal(7,2) NOT NULL,
  `origin` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`pid`, `pname`, `price`, `origin`) VALUES
(0, 'wasnotordered', '11.00', NULL),
(1, 'chocolate', '5.00', 'Belgium'),
(2, 'sugar', '0.75', 'India'),
(3, 'milk', '0.60', 'France'),
(4, 'tea', '10.00', 'India'),
(5, 'chocolate', '7.50', 'Switzerland'),
(6, 'milk', '1.50', 'France');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`cid`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`pid`,`cid`,`odate`),
  ADD KEY `orders_fk_cid` (`cid`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`pid`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_fk_cid` FOREIGN KEY (`cid`) REFERENCES `customers` (`cid`),
  ADD CONSTRAINT `orders_fk_pid` FOREIGN KEY (`pid`) REFERENCES `products` (`pid`);
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 */;

Answer 1

You can solve this by finding all the products that each cid has in common with the Smith customers. Then, just check that the count covers all the products:

select o2014.cid, count(distinct o2013.pid) as num_products,
       group_concat(distinct o2013.pid) as products
from orders o2013 join
     orders o2014
     on o2013.pid = o2014.pid and
        year(o2013.odate) = 2013 and year(o2014.odate) = 2014
where o2013.cid = (select c.cid from customers c where c.cname = 'Smith')
group by o2014.cid
having num_products = (select count(distinct o2013.products)
                       from orders o2013
                       where o2013.cid = (select c.cid from customers c where c.cname = 'Smith')
                      );

Answer 2

You can try something like the following. Basically force join the customers with all the products from smith of 2013, then LEFT JOIN with the products each customer bought of 2014. If both counts are equal means that all products from smith of 2013 were bought at least once in 2014, for each customer.

SELECT
    C.cid
FROM
    Customers C

    CROSS JOIN (
        SELECT DISTINCT
            P.pid
        FROM
            Customers C
            INNER JOIN Orders O ON C.cid = O.cid
            INNER JOIN Products P ON O.pid = P.pid
        WHERE
            C.cname = 'Smith' AND
            YEAR(O.odate) = 2013) X

    LEFT JOIN (
        SELECT DISTINCT
            C.cid,
            P.pid
        FROM
            Customers C
            INNER JOIN Orders O ON C.cid = O.cid
            INNER JOIN Products P ON O.pid = P.pid
        WHERE
            YEAR(O.odate) = 2014) R ON C.cid = R.cid AND X.pid = R.pid
GROUP BY
    C.cid
HAVING
    COUNT(X.pid) = COUNT(R.pid)

If you want to see the customers even if there are no products from smith of 2013, you can switch the CROSS JOIN for a FULL JOIN (...) X ON 1 = 1.