1

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
  • Thanks for actively editing, Table create statements and insert statements are preferred over pictures however. That makes it easier to copy and test. – Randi Vertongen Apr 16 at 20:10
  • Please check this about screenshots. – Vérace Apr 16 at 20:26
1

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
  • Thank you Randi! – Kevin Nguyen Apr 16 at 20:48
  • @KevinNguyen No problem! Glad it helped! What helped me was the previous edit with the data as text ;) – Randi Vertongen Apr 16 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.