Write SQL query that picks all rows except the ones that have specified value in three columns at the same time

user_10828673 0
Viewed 43

I have 3 columns -- GroupingKey, GroupingValue and GroupingType in a table as follows:

CREATE TABLE Grouping
    (`GroupingKey` varchar(8), `GroupingValue` varchar(7), `GroupingType` varchar(8));

INSERT INTO Grouping
    (`GroupingKey`, `GroupingValue`, `GroupingType`)
VALUES
    ('Language', 'Italian', 'Category'),
    ('Language', 'Russian', 'Unknown'),
    ('Unknown', 'Unknown', 'Unknown'),
    ('Phone', 'Unknown', 'Unknown');

What would be the SQL query to select all rows in the above table except the row that has Unknown, Unknown, Unknown in GroupingKey, GroupingValue and GroupingType respectively?

My expected output of the SQL query is:

GroupingKey GroupingValue GroupingType
Language Italian Category
Language Russian Unknown
Phone Unknown Unknown

I tried writing a query as follows:

SELECT DISTINCT groupingkey,
                groupingvalue,
                groupingtype
FROM   mytablename
WHERE  groupingkey NOT LIKE 'Unknown'
       AND groupingvalue NOT LIKE 'Unknown'
       AND groupingtype NOT LIKE 'Unknown'

But what is ending up happening is that it does not pick any row that has "Unknown" even in 1 of the 3 columns specified above

mysql sql
1 Answers

You can write the condition like this:

WHERE NOT (
            groupingkey = 'Unknown'
        AND groupingvalue = 'Unknown'
        AND groupingtype = 'Unknown'
          )

Note that for your requirement you should use = instead of LIKE.

Or:

WHERE groupingkey <> 'Unknown'
   OR groupingvalue <> 'Unknown'
   OR groupingtype <> 'Unknown'

which means that at least 1 of the columns must not be 'Unknown'.

Or:

WHERE (groupingkey, groupingvalue, groupingtype) <> ('Unknown', 'Unknown', 'Unknown')

See the demo.

user_104988280
Related