SQL: how to select where one column does not match another column for ALL records within a given gro-CodePudding

I have a table named sales in a MySQL database that looks like this:

company     manufactured    shipped
Mercedes    Germany         United States
Mercedes    Germany         Germany
Mercedes    Germany         United States
Toyota      Japan           Canada
Toyota      Japan           England
Audi        Germany         United States
Audi        Germany         France
Audi        Germany         Canada
Tesla       United States   Mexico
Tesla       United States   Canada
Tesla       United States   United States

Here is a Fiddle: http://www.sqlfiddle.com/#!17/145ff/3

I would like to return the list of companies that ship ALL of their products internationally (that is, where the value in the manufactured column differs from the value in the shipped column for ALL records of a particular company).

Using the example above, the desired result set would be:

company
Toyota
Audi

Here is my (hackish) attempt:

WITH temp_table AS (

                SELECT
                  s.company                                          AS company
                  , SUM(CASE
                           WHEN s.manufactured != s.shipped THEN 1
                           ELSE 0
                        END
                       )                                             AS count_international
                  , COUNT(s.company)                                 AS total_within_company

                FROM
                  sales s

                GROUP BY
                  s.company

              )

                SELECT
                   company
                FROM
                   temp_table
                WHERE count_international = total_within_company

Essentially, I count the instances where the columns do not match. Then I check whether the sum of those mismatched instances matches the number of records within a given group.

This approach works, but it's far from an elegant solution!

Can anyone offer advice as to a more idiomatic way to implement this query?

Thanks!

CodePudding user response：

We can GROUP BY company and use a HAVING clause to say all countries in shipped must differ to the country in manufactured:

SELECT company
FROM sales
GROUP BY company
HAVING COUNT(CASE WHEN manufactured = shipped THEN 1 END) = 0;

Try out here: db<>fiddle

The fiddle linked in the question is a Postgres DB, but MySQL is taged as DBMS.

In a MySQL DB, the above query can be simplified to:

SELECT company
FROM sales
GROUP BY company
HAVING SUM(manufactured = shipped) = 0;

In a Postgres DB, this is not possible.

CodePudding user response：

You have to think in sets... you want to display all without a match -- find the matches display the rest

 SELECT DISTINCT company 
 FROM sales
 WHERE company NOT IN (
   SELECT company 
   FROM sales 
   WHERE  manufactured = shipped
 )