# Delete companies with the least number of flights

I have solved the task of

Delete the companies that made the least number of flights.

the visual table diagram is here. The exercise is here

This is my source code -it's "arrow antipattern" , what is more elegant solution?

the inner most counts num of flights and groups by company, this is used to compare and find the actual minimum, then just select company names and delete.

DELETE FROM company
WHERE  name IN (SELECT name
FROM   (SELECT name
FROM   trip
INNER JOIN company
ON trip.company = company.id
GROUP  BY name
HAVING Count(trip.id) = (SELECT Min(kolv) AS mini
FROM   (SELECT
Count(trip.id) AS kolv
FROM   trip
INNER JOIN company
ON trip.company
=
company.id
GROUP  BY name
ORDER  BY kolv)k)) o)

• "companies" and "least"?? One company? Several? What cutoff do you want? Mar 29, 2021 at 7:12
• Delete from the table company only? Won't leave dangling references in trip? Maybe the task should be "Delete the company with the least number of flights, plus all associated information." But that would probably require 4 DELETEs, one per table. Mar 29, 2021 at 7:16
• The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?. Mar 29, 2021 at 7:24
• @RickJames According to the linked exercise, deleting from Company only is sufficient, even it leaves dangling references. I verified this by checking @ERJAN's provided query, which returned "Right answer". Mar 29, 2021 at 7:26
• Why do you do SELECT name FROM (SELECT name FROM trip? Mar 29, 2021 at 7:26

• If you look at the visual table diagram on the site you linked, for the Company table, the id is the primary key and therefore is guaranteed to be unique for each row in the table. name, on the other hand, is not guaranteed to be unique. So in your query, you should be grouping and deleting companies by their id, not by their name.
• Your query seems to be doing the work of counting the number of trips per company twice (once at the part HAVING Count(trip.id) and again at the part Count(trip.id) AS kolv) which is not necessary.
• As @Flavian and @RickJames have mentioned, refactoring your query to use Common Table Expressions will save it from the arrow anti-pattern, and make it much more readable.

An example refactor:

WITH TripsPerCompany AS (
SELECT company, COUNT(id) AS num_trips FROM Trip GROUP BY company
)

,CompaniesWithTheFewestTrips AS (
SELECT company
FROM TripsPerCompany
WHERE num_trips = (SELECT MIN(num_trips) FROM TripsPerCompany)
)

DELETE Company
FROM Company
JOIN CompaniesWithTheFewestTrips
ON Company.id = CompaniesWithTheFewestTrips.company;


If you can with MySQL 8.0, use the WITH Common Table Expression to simplify this query.

The idea is to extract out the nested queries into ones in the WITH statement before the main query and then use them just like tables in the main query.

• Use the multi-table flavor of DELETE. Why? To avoid the "arrow". And possibly the performance will be better.
• Avoid IN ( SELECT ... ); instead, try to use JOIN (or maybe LEFT JOIN). The Optimizer has been notoriously poor at optimizing IN (SELECT...). I don't know whether it will do a good job here -- please add EXPLAIN SELECT ....
• Alternatively, do the task in steps -- Create a temp table with, say, the innermost pair of queries, then use that temp in the rest. When doing this, be sure to explain what that temp table represents. I make this suggestion because my head is still spinning from the "arrow".
• Use CTE, if available, instead of a temp table. (There is a lot of overlap in the benefits/drawbacks between temp tables and CTEs. CTEs are the "modern" answer, where applicable.)
• Welcome to CodeReview. Could you please extend your suggestions with reasonings? Either by explaining yourself why should we prefer x over y Or by providing links about the related subjects. Mar 29, 2021 at 6:50
• @PeterCsala - Maybe my added text will help. No, I don't happen to have any link to share. Mostly I depend on 22 years of using MySQL. Mar 29, 2021 at 7:10
• That's perfect, thank you. Enjoy your time here. Mar 29, 2021 at 7:15