SQL Server – Monitoring Exceptions Between Parent and Child Tables

sql serversql-server-2016

enter code hereI need help with a query that I want to create. For instance, I have two tables (Process & Article).

Article is the parent table what has a unique article number and in the Process table there are processes created under an article number.

I want to create a query to monitor instances when an Article is Active and ALL the Process status of that article number is Inactive

Article Table

ArticleTitle                   ArticleNumber   ArticleStatus
-------------------------------------------------------------
 Expense Report                       1          Active
 Inbox Monitor                        2          Active
 Daily finance report                 3          Active
 Order Audit                          4          Active
 Order Tracking                       5          Inactive


Process Table

 ProcessTitle                   ArticleNumber   ProcessStatus
-------------------------------------------------------------
 Expense Report CPA                   1          Active
 Expense Report BG                    1          Active
 Inbox Monitor db                     2          Active
 Weekly finance report CPA            3          Inactive
 Weekly finance report BG             3          Inactive
 Order Audit for xyz                  4          Active
 Order Audit for abc                  4          Inactive

 Order Tracking                       5          Inactive

Best Answer

Test Data

CREATE TABLE dbo.Article([Article Title] NVARCHAR(50),
                         [Article Number] INT,  
                         [Article Status] NVARCHAR(20));

INSERT INTO dbo.Article([Article Title],
                        [Article Number],
                        [Article Status])
VALUES
('Expense Report',1,'Active'),
('Inbox Monitor',2,'Active'),
('Daily finance report',3,'Active'),
('Order Audit',4,'Active'),
('Order Tracking',5,'Inactive');


CREATE TABLE dbo.Process([Process Title] nvarchar(50), 
                         [Article Number] int,
                         [Process Status] nvarchar(20));

INSERT INTO  dbo.Process([Process Title], 
                         [Article Number],
                         [Process Status])
VALUES
('Expense Report CPA',1,'Active'),
('Expense Report BG',1,'Active'),
('Inbox Monitor db',2,'Active'),
('Weekly finance report CPA',3,'Inactive'),
('Weekly finance report BG',3,'Inactive'),
('Order Audit for xyz',4,'Active'),
('Order Audit for abc',4,'Inactive'),
('Order Tracking',5,'Inactive');

Query

SELECT * 
FROM dbo.Article A
WHERE [Article Status] = 'Active'
AND NOT EXISTS 
(SELECT * FROM dbo.Process P WHERE  P.[Article Number] = A.[Article Number] 
                                    AND P.[Process Status] != 'Inactive');

Result

Article Title         Article Number    Article Status
Daily finance report  3                 Active
Related Question