SQL - Filter by two matching columns AND one different column-CodePudding

Below I have created a sample table to reflect the problem I face. I am trying to figure out how I can collect data that contains two pieces of matching data and one piece that is different. In this situation I want to find only the information with both a matching date and matching group number while simultaneously having a different language option. If the dates match but the groups do not they should be left out of the result and the same can be said if all three (Group, Date, Language) are matching.

Example Table:

NAME | GROUP | DATE | LANGUAGE
Anne     3    1/1/21  en
John     3    2/4/21  en
Kyle     2    1/1/21  es 
Jake     3    3/8/21  es
Stacy    1    5/9/21  fr 
Mike     2    3/3/21  fr
Paul     2    1/1/21  fr 
Brian    1    5/9/21  en
Mark     3    1/1/21  en

Desired output:

NAME | GROUP | DATE | LANGUAGE
Kyle     2    1/1/21  es 
Paul     2    1/1/21  fr 
Stacy    1    5/9/21  fr 
Brian    1    5/9/21  en

CodePudding user response：

I think this query does it

SELECT NAME, GROUP, DATE, LANGUAGE
FROM Table t1
WHERE EXISTS (SELECT 1 FROM Table t2 
              WHERE t1.DATE = t2.DATE 
                   AND t1.GROUP = t2.GROUP 
                   AND t1.LANGUAGE != t2.LANGUAGE)

CodePudding user response：

Here is one aggregation approach:

SELECT t1.NAME, t1."GROUP", t1.DATE, t1.LANGUAGE
FROM yourTable t1
INNER JOIN
(
    SELECT "GROUP", DATE
    FROM yourTable
    GROUP BY "GROUP", DATE
    HAVING MIN(LANGUAGE) <> MAX(LANGUAGE)
) t2
    ON t2."GROUP" = t1."GROUP" AND
       t1.DATE = t2.DATE;

CodePudding user response：

You can do it in a single table scan using analytic functions:

SELECT name,
       "GROUP",
       "DATE",
       language
FROM   (
  SELECT t.*,
         COUNT(DISTINCT language) OVER (PARTITION BY "GROUP", "DATE") AS num_langs
  FROM   table_name t
)
WHERE  num_langs > 1;

Which, for the sample data:

CREATE TABLE table_name (NAME, "GROUP", "DATE", LANGUAGE) AS
SELECT 'Anne',  3, DATE '2021-01-01', 'en' FROM DUAL UNION ALL
SELECT 'John',  3, DATE '2021-02-04', 'en' FROM DUAL UNION ALL
SELECT 'Kyle',  2, DATE '2021-01-01', 'es' FROM DUAL UNION ALL
SELECT 'Jake',  3, DATE '2021-03-08', 'es' FROM DUAL UNION ALL
SELECT 'Stacy', 1, DATE '2021-05-09', 'fr' FROM DUAL UNION ALL
SELECT 'Mike',  2, DATE '2021-03-03', 'fr' FROM DUAL UNION ALL
SELECT 'Paul',  2, DATE '2021-01-01', 'fr' FROM DUAL UNION ALL
SELECT 'Brian', 1, DATE '2021-05-09', 'en' FROM DUAL UNION ALL
SELECT 'Mark',  3, DATE '2021-01-01', 'en' FROM DUAL;

Outputs:

NAME GROUP DATE LANGUAGE

Brian 1 09-MAY-21 en

Stacy 1 09-MAY-21 fr

Kyle 2 01-JAN-21 es

Paul 2 01-JAN-21 fr

NAME	GROUP	DATE	LANGUAGE
Brian	1	09-MAY-21	en
Stacy	1	09-MAY-21	fr
Kyle	2	01-JAN-21	es
Paul	2	01-JAN-21	fr

db<>fiddle here