1
\$\begingroup\$

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) 
\$\endgroup\$
5
  • 1
    \$\begingroup\$ "companies" and "least"?? One company? Several? What cutoff do you want? \$\endgroup\$ – Rick James Mar 29 at 7:12
  • \$\begingroup\$ 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. \$\endgroup\$ – Rick James Mar 29 at 7:16
  • 1
    \$\begingroup\$ 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?. \$\endgroup\$ – BCdotWEB Mar 29 at 7:24
  • \$\begingroup\$ @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". \$\endgroup\$ – Setris Mar 29 at 7:26
  • \$\begingroup\$ Why do you do SELECT name FROM (SELECT name FROM trip? \$\endgroup\$ – BCdotWEB Mar 29 at 7:26
3
\$\begingroup\$

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

https://dev.mysql.com/doc/refman/8.0/en/with.html

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.

\$\endgroup\$
3
\$\begingroup\$
  • 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;
\$\endgroup\$
2
\$\begingroup\$
  • 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.)
\$\endgroup\$
3
  • \$\begingroup\$ 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. \$\endgroup\$ – Peter Csala Mar 29 at 6:50
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ – Rick James Mar 29 at 7:10
  • \$\begingroup\$ That's perfect, thank you. Enjoy your time here. \$\endgroup\$ – Peter Csala Mar 29 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.