My example query:
Select TBL1.caseid, TBL1.anotherid, TBL2.locked
From atable1 AS TBL1
JOIN atable2 AS TBL2 ON TBL2.caseid = TBL1.caseid
where TBL1.date Between CAST('2021-03-19' as Date) and CAST('2023-03-30' as Date)
and TBL1.anotherid = '989142'
and TBL1.caseid IS NOT NULL
Gives me:
caseid | anotherid | locked
---------------------------
98461 | 989142 | 2
98461 | 989142 | 2
98461 | 989142 | 2
98461 | 989142 | 0
98461 | 989142 | 0
98461 | 989142 | 1
98461 | 989142 | 1
98461 | 989142 | 1
98461 | 989142 | 1
98461 | 989142 | 1
If the results have a lock of 1 then I do not want to show any of the other information. However, when I do this:
Select TBL1.caseid, TBL1.anotherid, TBL2.locked
From atable1 AS TBL1
JOIN atable2 AS TBL2 ON TBL2.caseid = TBL1.caseid
where TBL1.date Between CAST('2021-03-19' as Date) and CAST('2023-03-30' as Date)
and TBL1.anotherid = '989142'
and TBL2.locked != 1
and TBL.caseid IS NOT NULL
I get these results:
caseid | anotherid | locked
---------------------------
98461 | 989142 | 2
98461 | 989142 | 2
98461 | 989142 | 2
98461 | 989142 | 0
98461 | 989142 | 0
I am needing it to not show anything (no results) for that query above since it did have a lock of 1.
I was thinking of doing an IIF but I can not seem to get the logic correct.
July 1, 2024 at 3:50 pm
That query can't run, because you didn't specify which "caseid" in the SELECT.
We have NO way to know which column(s) are in which tables. You need to alias EVERY column in your query.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 1, 2024 at 4:04 pm
WITH bunch
AS (SELECT caseid
,anotherid
,locked
FROM atable1
JOIN atable2
ON atable2.caseid = atable1.caseid
WHERE atable1.date
BETWEEN CAST ('2021-03-19' AS DATE) AND CAST ('2023-03-30' AS DATE)
AND anotherid = '989142'
AND atable2.caseid IS NOT NULL)
SELECT bunch.caseid, bunch.anotherid, bunch.locked
FROM bunch
WHERE NOT EXISTS
(
SELECT 1 FROM bunch b2 WHERE b2.locked = 1
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 1, 2024 at 5:58 pm
You just need a NOT EXISTS in the original query, but can't be coded accurately without knowing which columns are in which table(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 1, 2024 at 6:06 pm
Alright I corrected my OP query.
Select TBL1.caseid, TBL1.anotherid, TBL2.locked
From atable1 AS TBL1
JOIN atable2 AS TBL2 ON atable2.caseid = atable1.caseid
where TBL1.date Between CAST('2021-03-19' as Date) and CAST('2023-03-30' as Date)
and not exists(select * from atable2 AS TBL2 where TBL2.caseid = TBL1.caseid and TBL2.locked = 1)
and TBL1.anotherid = '989142'
and TBL1.caseid IS NOT NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 1, 2024 at 6:54 pm
Thanks Scott - got it working with the help of your query code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply