I don't even know how to ask this so I can google, so my apologies in advance. I have a SQL table where it looks something like this

col1 col2 col3
a    1    name1
a    2    name1
a    3    name1
a    4    name1
b    1    name1
b    2    name1
b    3    name1

If you notice, there are 4 rows for col1=a for each col2 value[1-4]. There is NOT, however, a record where col1=b and col2=4.

With grouping and count<4 I can return rows that exist, but how can I return something like 'b,4,name1' for a row (my missing record)

SELECT * FROM
(
SELECT T.COL1, T.COL3, COUNT(T.COL2) COL2_COUNT, STRING_AGG(T.COL2,',') COL2_LIST
FROM
(
SELECT F.COL1, F.COL3, F.COL2 FROM TBL F 
) T
GROUP BY T.COL1, T.COL3
) J WHERE J.COL2_COUNT < 4

CodePudding user response：

All unique combinations of Col1&Col3 is to have a row for each unique col2 in the table:

select UCol1_2.col1, UCol2.col2, UCol1_2.Col3
from (
      select distinct col2
      from yourtable
     ) UCol2
     cross join
     (
      select distinct col1, col3
      from yourtable
     ) UCol1_2

     left join
     yourtable a
     on a.col2=UCol2.col2
     and a.Col1=UCol1_2.Col1
     and a.Col3=UCol1_2.Col3
where a.col2 is null

CodePudding user response：

I noticed after doing this you're looking for MSSQL. If the question becomes live again I'll put in the MSSQL version.

The basic idea is to create a cross join and then filter out what is missing by testing for filter.col1 is null.

Schema (MySQL v5.7)

    CREATE TABLE stack_overflow_72485200
        (`col1` varchar(1), `col2` int, `col3` varchar(5))
    ;
        
    INSERT INTO stack_overflow_72485200
        (`col1`, `col2`, `col3`)
    VALUES
        ('a', 1, 'name1'),
        ('a', 2, 'name1'),
        ('a', 3, 'name1'),
        ('a', 4, 'name1'),
        ('b', 1, 'name1'),
        ('b', 2, 'name1'),
        ('b', 3, 'name1')
    ;

Query #1

    select candidate_base.* from (
        select candidate.col1, base.col2, base.col3 from (
        select col1 
        from stack_overflow_72485200 missing
        group by col1
      ) candidate, 
      (
        select col2, col3 
        from stack_overflow_72485200 base
        group by col2, col3 ) base 
      ) candidate_base
      left join stack_overflow_72485200 filter
      on candidate_base.col1 = filter.col1
      and candidate_base.col2 = filter.col2
      and candidate_base.col3 = filter.col3
      where filter.col1 is null;
      

View on DB Fiddle