October 24, 2013 at 5:32 am
I am trying to exclude ID's that appear in the derived table
SELECT Maxdate.Date,db1.CASEID
FROM db1 RIGHT OUTER JOIN
(SELECT CASE_ID, MAX(DATE) as [MY_DATE],FIELDA,FIELDB FROM dbo.DB2 WHERE FIELDA = 1 AND FIELDB IS NULL GROUP BY CASE_ID,FIELDA,FIELDB) AS MaxDate ON MaxDate.CASE_ID = db1.CASEID
WHERE (db1.DESCRIPTION = N'Open')
How do I do that?
Really stuck on exluding based on Case ID from this table
(SELECT CASE_ID, MAX(DATE) as [MY_DATE],FIELDA,FIELDB FROM dbo.DB2 WHERE FIELDA = 1 AND FIELDB IS NULL GROUP BY CASE_ID,FIELDA,FIELDB)
October 24, 2013 at 8:19 am
Hm. I believe something like this should work:
SELECT CASE_ID, MAX(DATE) as [MY_DATE],FIELDA,FIELDB
INTO #Temp
FROM dbo.DB2 WHERE FIELDA = 1 AND FIELDB IS NULL GROUP BY CASE_ID,FIELDA,FIELDB)
SELECT Maxdate.Date,db1.CASEID
FROM db1
WHERE (db1.DESCRIPTION = N'Open')
AND CASEID NOT IN (SELECT CASE_ID FROM #Temp)
Basically, you want to put the IDs you want to exclude into some form of storage (either a CTE or a temp table; since you had a SELECT already written here, a temp table is an easy go-to with a SELECT... INTO), then use the NOT IN query clause to exclude the CASE_IDs from the stored SELECT.
- 😀
October 24, 2013 at 8:37 am
So, if I understand your request you want to return all rows from db1 where the CASEID is not in the derived table? If that is true you can simple change you right outer join to a left and add a MaxDate.case_id is null:
SELECT Maxdate.Date,db1.CASEID
FROM db1 left OUTER JOIN
(SELECT CASE_ID, MAX(DATE) as [MY_DATE],FIELDA,FIELDB FROM dbo.DB2 WHERE FIELDA = 1 AND FIELDB IS NULL GROUP BY CASE_ID,FIELDA,FIELDB) AS MaxDate ON MaxDate.CASE_ID = db1.CASEID
WHERE (db1.DESCRIPTION = N'Open')
and MaxDate.case_id is null
If you really want to look into the different ways to accomplish this and which one might be faster read Gail's article on the subject here:
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
October 24, 2013 at 9:49 am
This looks weird, You want to exclude ids from your derived table but you're using a column from your derived table. You will only get NULLS for the date.
To provide useful responses, you need to give us some sample data and DDL. For information on how to do this, read the article linked in my signature.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply