SQL - How to only retrieve a record from a sub-query if all the records have a particular value

user_1144056 0
Viewed 61

Given 2 tables.

PR 

prnum | col2        | col3
-------------------------
 1001 | Khar        | 5
 2002 | SantaCruz   | 3
 3200 | Sion        | 2
 4321 | VT          | 1

and

PRLine

prnum | prlinenum | status
------------------------------
 1001 | 1         | INWILLCALL
 1001 | 2         | ORDERED
 2002 | 1         | ORDERED
 2002 | 2         | ORDERED
 2002 | 3         | ORDERED
 3200 | 1         | INWILLCALL
 3200 | 2         | INWILLCALL

I would like to select all the PRNUM's from PR table, where ALL of its corresponding records contained in PRLINE table have status of INWILLCALL.

In the tables above, only PRNUM 3200 should be returned.

so far, I have:

select * from PR WHERE
status not in ('CAN','CLOSED','COMP','DRAFT')
and prnum in (select prnum from prline where status = 'INWILLCALL');

which is obviously wrong.

Can someone help please ?

Thank you

aggregate-functions sql-server sql
6 Answers

You can try to use condition aggregate function in HAVING to get prnum which status is all 'INWILLCALL'

select *
from PR 
WHERE prnum  IN (
    SELECT prnum 
    FROM PRLine
    GROUP BY prnum
    HAVING COUNT(CASE WHEN status = 'INWILLCALL' THEN 1 END) = COUNT(*)
)

sqlfiddle

user_51760710

A few other options you could try:

DECLARE @PR TABLE
    (
        [prnum] INT
      , [col2] NVARCHAR(100)
      , [col3] INT
    );
INSERT INTO @PR (
                    [prnum]
                  , [col2]
                  , [col3]
                )
VALUES ( 1001, ' Khar        ', 5 )
     , ( 2002, ' SantaCruz   ', 3 )
     , ( 3200, ' Sion        ', 2 )
     , ( 4321, ' VT          ', 1 );

DECLARE @PRLine TABLE
    (
        [prnum] INT
      , [prlinenum] INT
      , [status] NVARCHAR(100)
    );
INSERT INTO @PRLine (
                        [prnum]
                      , [prlinenum]
                      , [status]
                    )
VALUES ( 1001, 1, 'INWILLCALL' )
     , ( 1001, 2, 'ORDERED' )
     , ( 2002, 1, 'ORDERED' )
     , ( 2002, 2, 'ORDERED' )
     , ( 2002, 3, 'ORDERED' )
     , ( 3200, 1, 'INWILLCALL' )
     , ( 3200, 2, 'INWILLCALL' );


--Option 1
SELECT     [a].[prnum]
FROM       @PR [a]
INNER JOIN @PRLine [b]
    ON [b].[prnum] = [a].[prnum]
GROUP BY   [a].[prnum]
HAVING     MIN([b].[status]) = MAX([b].[status])
           AND MIN([b].[status]) = 'INWILLCALL';

--Option 2
SELECT     DISTINCT [a].[prnum]
FROM       @PR [a]
INNER JOIN @PRLine [b]
    ON [b].[prnum] = [a].[prnum]
WHERE      [b].[status] = 'INWILLCALL'
           AND [a].[prnum] NOT IN (
                                      SELECT [prnum]
                                      FROM   @PRLine
                                      WHERE  [status] <> 'INWILLCALL'
                                  );
user_103765370

Just one more option: a correlated subquery using WITH NOT EXISTS. This guards you against issues with NOT IN in the event of a NULL value in status.

select 
  prnum
from 
  @PRLine as prl
where 
  not exists 
    (select 1 
     from 
      @PRLine prl2 
     where 
      prl2.status <> 'INWILLCALL' 
      and prl2.prnum = prl.prnum)
group by prnum;
user_57905840

I would use GROUP BY clause :

SELECT prnum
FROM PRLine 
GROUP BY prnum
HAVING SUM(CASE WHEN Status <> 'INWILLCALL' THEN 1 ELSE 0 END) = 0;

Other option would be to use aggregate function twise:

SELECT prnum
FROM PRLine 
GROUP BY prnum
HAVING MIN(Status) = MAX(Status) AND MIN(Status) = 'INWILLCALL';
user_55294450

You can try this too, just another way:

SELECT DISTINCT  prnum FROM
(SELECT  'INWILLCALL' AS te,p.prnum,pr.status,lead(pr.status) OVER (PARTITION BY p.prnum ORDER BY pr.status)leadvalue FROM #PR AS p
INNER JOIN #PRLine  as pr   ON p.prnum = pr.prnum)m
WHERE leadvalue = status
AND status = 'INWILLCALL'
user_15505100

SELECT prnum FROM pr JOIN prline on pr.prnum = prline.prnum WHERE prline.status = 'inwillcall' GROUP BY prnum

The join will make sure you are getting joint entries from the two tables. The filter condition on the second table will ensure only those with "inwillcall" will be returned. The group by is a way to filter out duplicates. Hope this helps!

user_105537510
Related