SQL Server WITH clause, how to add additional condition?

Question

Regarding to this post and answer: https://stackoverflow.com/a/1389653/9343200

What I'm trying to do is to find missing IDs in table Paragony then I have to check one more column: par_nrkasy if it's =1 or =2.

I'm trying to add one more condition WHERE par_nrkasy = but I have no clue how to add to this query, I mean, to make it works and keep the meaning of primary code.

My code:

;WITH Missing (missnum, maxid) AS 
(
    SELECT 
        1 AS missnum,
        (SELECT MAX(par_numerf) 
         FROM Paragony) 
    UNION ALL 
    SELECT missnum + 1, maxid 
    FROM Missing 
    WHERE missnum < maxid
)
SELECT missnum 
FROM Missing 
LEFT OUTER JOIN Paragony tt ON tt.par_numerf = Missing.missnum 
WHERE tt.par_numerf is NULL 
OPTION (MAXRECURSION 0);

Any ideas? Anything I tried I got wrong results like duplicates or not results at all

Alternatively I could have use it through this code, but problem is the same how to add clause

SELECT DISTINCT par_numerf + 1
FROM Paragony
WHERE par_numerf + 1 NOT IN (SELECT DISTINCT par_numerf FROM Paragony);

Please provide sample data and desired results *in your question*. It should stand on its own without references through links. Just to be clear: There is no `par_nrkasy` either in your question or in the question you are referring to, so what you want to do is baffling. — Gordon Linoff, Aug 31 '20 at 11:31
Tally table solutions get no love on Stack Overflow but that's what this calls for. — SteveC, Aug 31 '20 at 12:15
The simplest thing to do BEFORE you start writing code is to search the internet. You need sequential list of numbers from 1 to some upper boundary determined by a query? Fine - search [numbers table](https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table). — SMor, Aug 31 '20 at 12:16
Next, learn to ask smart questions. Phrases like "doesn't work" and " wrong results" are not meaningful to anyone but you. Actual examples of your source data and what you desire from your query based on your sample help others understand your goal. — SMor, Aug 31 '20 at 12:20
Thanks, basically what I'm doing it's finding in DB "missing" IDs like here: https://imgur.com/U944wBB , but the problem is that I have to addidtional include condition to check another column par_nrkasy if it's 1 or 2 because on 1 there is no missing id in this field but on 2 it is — l1m4k, Aug 31 '20 at 12:23

Random User · Answer 1 · 2020-08-31T12:06:56.847

0

Maybe you could try something like this :

;WITH Missing (missnum, maxid) AS (
SELECT 1 AS missnum, (
    SELECT MAX(par_numerf) 
    FROM Paragony) 
UNION ALL 
SELECT missnum + 1, maxid 
FROM Missing 
WHERE missnum < maxid) 
SELECT missnum 
FROM Missing 
LEFT OUTER JOIN Paragony tt 
ON tt.par_numerf = Missing.missnum 
WHERE tt.par_numerf is NULL 
OR tt.par_nrkasy = 2 
OPTION (MAXRECURSION 0);

edited Aug 31 '20 at 12:06

answered Aug 31 '20 at 11:35

Random User

341
3
6

Then I got 0 results and it should showed atleast one where par_numerf = 175 , like you can see here, it's missing ID (numerf) 175: https://imgur.com/U944wBB – l1m4k Aug 31 '20 at 11:41
A record with the field par_numerf with a value different from NULL should already be filtered from the where clause tt.par_numerf is NULL. When talking about additional condition, are you talking about OR or AND condition? – Random User Aug 31 '20 at 11:44
The thing is I have to add this condition because in my database when I don't check if par_nrkasy its 1 or 2, then it doesn't show me that this row not exist in db, for example when I made select without this condition it doesn't show me that this id (175) is missing bc on par_nrkasy = 1 its exists, but in par_nrkasy = 2 NOT and I have to consider it, not sure how it should be in condition tho – l1m4k Aug 31 '20 at 11:51
If i understand correctly, you want the query to return each id from "missing" record, or records with a value in par_nrkasy equal to 2? – Random User Aug 31 '20 at 11:56
I want query which return all "missing" ids (par_numerf), but thats why I need to include this column (par_nrkasy) because without it, it might be situtation like I described above that it doesnt show "missing" id bc it was included on par_nrkasy = 1 but it's missing on par_nrkasy = 2 – l1m4k Aug 31 '20 at 12:02
Not really, I mean it show correct records, but another problem now are duplicates https://imgur.com/0MaAeGo https://imgur.com/aTpfiaw for every record now are X duplicates, dunno why – l1m4k Aug 31 '20 at 12:18
A quick work around would be to use SELECT DISTINCT missnum but there's probably a better way to do it... – Random User Aug 31 '20 at 12:25

score 0 · Answer 2 · answered Aug 31 '20 at 12:23

The function fnTally can be found here. Something like this

;with max_min_cte(max_par_numerf, min_par_numerf) as (
    select max(par_numerf), min(par_numerf)
    from Paragony)
select f.N 
from max_min_cte mmc 
     cross apply dbo.fnTally(1, mmc.max_par_numerf) f
except
select par_numerf 
from Paragony
where par_nrkasy in(1,2);

SQL Server WITH clause, how to add additional condition?

2 Answers2