I'm working with the classicmodels database, originally for MySQL, but which we're using with SQLite. Within this database, there are 2 tables of interest, the orderdetails table... ... and the products table. I've written a query to list all the distinct order numbers, along with the product lines of the products within their orders. Here is the query, with the

SQLite – Excluding rows from a query based on certain column values, without using correlated subqueries

I’m working with the classicmodels database, originally for MySQL, but which we’re using with SQLite. Within this database, there are 2 tables of interest, the orderdetails table…

CREATE TABLE `orderdetails` (
  `orderNumber` int(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int(11) NOT NULL,
  `priceEach` decimal(10,2) NOT NULL,
  `orderLineNumber` smallint(6) NOT NULL,
  PRIMARY KEY (`orderNumber`,`productCode`)
);

… and the products table.

CREATE TABLE `products` (
  `productCode` varchar(15) NOT NULL,
  `productName` varchar(70) NOT NULL,
  `productLine` varchar(50) NOT NULL,
  `productScale` varchar(10) NOT NULL,
  `productVendor` varchar(50) NOT NULL,
  `productDescription` text NOT NULL,
  `quantityInStock` smallint(6) NOT NULL,
  `buyPrice` decimal(10,2) NOT NULL,
  `MSRP` decimal(10,2) NOT NULL,
  PRIMARY KEY (`productCode`)
);

I’ve written a query to list all the distinct order numbers, along with the product lines of the products within their orders. Here is the query, with the first few rows of the corresponding output.

sqlite> SELECT DISTINCT orderdetails.orderNumber, products.productLine
> FROM orderdetails JOIN products
> ON orderdetails.productCode == products.productCode;

orderNumber  productLine 
-----------  ------------
10100        Vintage Cars
10101        Vintage Cars
10102        Vintage Cars
10103        Classic Cars
10103        Trucks and B
10103        Vintage Cars
10104        Classic Cars
10104        Trucks and B
10104        Trains      
10105        Classic Cars
10105        Vintage Cars
10105        Trains      
10105        Ships       
10106        Planes      
10106        Ships       
10106        Vintage Cars
10107        Motorcycles

Now, I want to exclude all rows which contain order numbers corresponding to orders which contain Planes. For example, in the output above, order number 10106 contains products that are either Planes or Ships – since 10106 represents an order which contains planes (among other things), BOTH of these rows should be removed in such a query.

A simple subquery approach yields the right answer…

SELECT orderNumber FROM orders EXCEPT
SELECT DISTINCT orderdetails.orderNumber FROM orderdetails JOIN products
ON orderdetails.productCode == products.productCode
WHERE products.productLine == "Planes";

… however the catch here, is that I can’t use correlated subqueries – the question that I am trying to tackle has explicitly stated that these subqueries are not allowed.

What have I tried?

A simple exclusion clause (such as WHERE products.productLine != "Planes") won’t do the trick, as this only removes the order numbers of orders which ONLY contain planes. If an order contains planes and ships (for example), that number will remain in the query – not good!

My initial research into similar questions on StackOverflow seems only to bring up answers suggesting the use of subqueries (which would be awesome in other situations, but unfortunately not in this problem – we’re avoiding subqueries).

Answer

You say “the catch here is that I can’t use correlated subqueries”. But the query that you say yields the right answer, namely…

SELECT orderNumber FROM orders EXCEPT
SELECT DISTINCT orderdetails.orderNumber FROM orderdetails JOIN products
ON orderdetails.productCode == products.productCode
WHERE products.productLine == "Planes";

… does not, in fact, contain a correlated subquery. In fact it doesn’t contain a subquery at all, if you’re going by the true definition of what a subquery is. So if the restriction really is “don’t use subqueries”, then the above query actually abides by the restriction, and is fine.

Advertisement

Answer