WHERE NOT IN???

[tqeonline]

I have two queries that link to two gridviews.

I want the second gridview to display results that ARE NOT IN the first gridview.

Query 1

SELECT [ID]
   ,[EMAIL] as 'E-mail'      
   ,[REL_NAME] as 'Release Name'
   ,SQA_DASH_Report_Email.[REL_ID]
FROM SQA_DASH_Report_Email, RELEASES
WHERE SQA_DASH_Report_Email.[REL_ID] = RELEASES.[REL_ID]
AND [EMAIL] = @EMAIL

Query 2

WITH tmp_Table AS
(SELECT
REL_ID AS 'Release ID',
REL_NAME as 'Release Name',
CONVERT (varchar(10), CAST(REL_USER_02 AS DATETIME),110) AS 'Production Date',

ISNULL(round(CAST(cast(CYCLE_PASSED_COUNT.CNT as float)
/
CYCLE_SCRIPT_COUNT.CNT as float)* 100,1),0) AS 'Actual %',

ISNULL(CYCLE_PASSED_COUNT.CNT,0) AS 'Passed Scripts',
ISNULL(CYCLE_SCRIPT_COUNT.CNT,0) AS 'Total Scripts'

FROM

            RELEASES INNER JOIN RELEASE_CYCLES WITH (NOLOCK)
            ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID

            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT
                FROM TESTCYCL WITH (NOLOCK)
                WHERE TC_STATUS <> 'N/A'
                GROUP BY TC_ASSIGN_RCYC) AS CYCLE_SCRIPT_COUNT
            ON RELEASE_CYCLES.RCYC_ID = CYCLE_SCRIPT_COUNT.TC_ASSIGN_RCYC

            LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT
                FROM TESTCYCL WITH (NOLOCK)          
                GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT      
            ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC

            LEFT OUTER JOIN (SELECT BG_DETECTED_IN_RCYC, COUNT(*) AS CNT FROM BUG WITH (NOLOCK)
                WHERE (BUG.BG_STATUS <> 'Invalid' 
                AND BUG.BG_STATUS <> 'Closed' 
                AND BUG.BG_STATUS <> 'New' 
                AND BUG.BG_STATUS <> 'Deferred')
                GROUP BY BG_DETECTED_IN_RCYC) AS BUG
            ON BUG.BG_DETECTED_IN_RCYC = RCYC_ID)        

SELECT [Release ID],[Release Name]
                
FROM tmp_table WITH (NOLOCK)
GROUP BY [Release ID],[Release Name],[Production Date]
HAVING ([Production Date] >= DATEADD(DAY, -3, GetDate()) 
OR 
(CASE WHEN COUNT(*) = 1 THEN 
SUM([Actual %]) ELSE
Round(CAST(SUM([Passed Scripts])AS DECIMAL)/CAST(SUM(case when isnull([Total Scripts],1) = 0 then 1 else [Total Scripts] end) AS DECIMAL)*100,1)
                 END) < '100')
AND cast([Production Date] as datetime) > '10/1/2010'
ORDER BY [Release ID]

I just want that second one to display everything not in the first one.

REL_ID would be the linking factor. I'm just kind of confused how to do it.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

GOT IT!

WHERE NOT EXISTS (SELECT *
				FROM SQA_DASH_Report_Email
				WHERE SQA_DASH_Report_Email.Rel_ID = tmp_table.[Release ID]
				AND [Email] = @EMAIL)

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[bobchuckjim]

a left join is another option...

LEFT JOIN SQA_DASH_Report_Email
ON SQA_DASH_Report_Email.Rel_ID = tmp_table.[Release ID]
WHERE SQA_DASH_Report_Email.Rel_ID is null

...but you'd have to check whether its really any faster and less/more readable