SQL Get Records based on multiple row value-CodePudding

I am trying to create a SQL report that can do logic based on a boolean that exists in multiple rows. I created a bunch of fake data below, and my attempts.

IMPORT NOTE: I am writing this using SSMS but plan to run it in MySql. Ran into some issues with temporary tables and "cannot re-open table..." so I decided to work with a familiar environment and work on porting things over as I have breakthroughs.

The way it exists in the DB is by joining two tables, lets call them display and displayschedule.

a) display

CREATE TABLE #tmp_display (
    DisId int,
    DisName varchar(255),
    DisActive tinyint,
    Descr varchar(255),
    DateModified datetime
)

INSERT INTO #tmp_display(DisId, DisName, DisActive, Descr, DateModified)
VALUES (4210, 'Test Display 1', 1, 'Test Display 1', GETDATE()),
       (1241, 'Test Display 2', 1, 'Test Display 2', GETDATE()),
       (8311, 'Test Display 3', 1, 'Test Display 3', GETDATE())

b) displayschedule

CREATE TABLE #tmp_displayschedule (
    DisSchedId int,
    DisId int,
    DisSchedName varchar(255),
    DisSchedAct tinyint,
    Descr varchar(255),
    DateModified datetime
)

INSERT INTO #tmp_displayschedule(DisSchedId, DisId, DisSchedName, DisSchedAct, Descr, DateModified)
VALUES (159123, 4210, 'Test Schedule 0', 0, 'OLD Schedule for Display 1 & 2', GETDATE() -1),
       (159123, 1241, 'Test Schedule 0', 0, 'OLD Schedule for Display 1 & 2', GETDATE() -1),
       (160213, 4210, 'Test Schedule 1', 0, 'Schedule for Display 1 & 2', GETDATE()),
       (160213, 4210, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', GETDATE()),
       (160213, 1241, 'Test Schedule 1', 1, 'Schedule for Display 1 & 2', GETDATE()),
       (160112, 8311, 'Test Schedule 2', 0, 'Schedule for Display 3', GETDATE()),
       (160112, 8311, 'Test Schedule 2', 1, 'Schedule for Display 3', GETDATE())

See the joined data:

SELECT * 
FROM #tmp_display dis
LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
ORDER BY ds.DateModified ASC

My attempt at a self join to get expected output:

SELECT t1.DisId
FROM (
        SELECT dis.DisId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 0
        GROUP BY dis.DisId
     ) as t1
JOIN
    (
        SELECT dis.DisId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 1
        GROUP BY dis.DisId
     ) as t2 on t1.DisId = t2.DisId

My Current Output:

DisId
1241
4210
8311

My Expected Output:

DisId
4210
8311

What is causing the issue (i think): The fact that there is an OLD schedule id for DisId 1241 that has an inactive record but the fact that it exists in another schedule id and is active Im not sure how to factor this out.

I tried adding in the DisSchedId into the GROUP BY which gave me my result in SSMS, but did not give me anything in MySql.

SELECT t1.DisId
FROM (
        SELECT dis.DisId, ds.DisSchedId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 0
        GROUP BY dis.DisId, ds.DisSchedId
     ) as t1
JOIN
    (
        SELECT dis.DisId, ds.DisSchedId
        FROM #tmp_display dis
        LEFT OUTER JOIN #tmp_displayschedule ds on dis.DisId = ds.DisId
        WHERE ds.DisSchedAct = 1
        GROUP BY dis.DisId, ds.DisSchedId
     ) as t2 on t1.DisId = t2.DisId and t1.DisSchedId = t2.DisSchedId

CodePudding user response：

Tested on MySQL 8.0.29:

mysql> select d.DisId from tmp_display d 
       join tmp_displayschedule s on d.DisId = s.DisId 
       group by d.DisId, s.DisSchedId 
       having group_concat(distinct s.DisSchedAct order by s.DisSchedAct) = '0,1';
 ------- 
| DisId |
 ------- 
|  4210 |
|  8311 |
 -------

That works for your sample data and desired result, but it may need some refinement for other cases.

Anyway, it demonstrates one solution for relational-division, which is the general type of task you're trying to solve. Relational division is where you need a set of rows to all include specific values. It's sort of the complement to IN() but where the terms are combined with AND instead of OR. You can follow that tag to see other past answers on Stack Overflow about relational division.