Philippe Renaud
asked on
SQL Select Query help
Hello EE,
I have a select Query that, when a user search for a range of numbers lets say :
FROM ID 1 to ID 15
right now in my query, if ID 9 and 10 does not exist at all, in my result I will show only the IDs are obviously exists like this
1
2
3
4
5
6
7
8
11
12
what I would like, is that, I would like to show 9 and 10 with a note "DELETED" so that the user knows why its not there anymore.
my query starts with SELECT * from myMainIDTable m and some other inner join / left join for other informaiton
and in my WHERE clause I do WHERE m.ID >= txtFrom.Value and m.ID <= txtTO.Value
so if the ID is not in M, it will not show, but I would like to show with a note.
how can I do that ?
I have a select Query that, when a user search for a range of numbers lets say :
FROM ID 1 to ID 15
right now in my query, if ID 9 and 10 does not exist at all, in my result I will show only the IDs are obviously exists like this
1
2
3
4
5
6
7
8
11
12
what I would like, is that, I would like to show 9 and 10 with a note "DELETED" so that the user knows why its not there anymore.
my query starts with SELECT * from myMainIDTable m and some other inner join / left join for other informaiton
and in my WHERE clause I do WHERE m.ID >= txtFrom.Value and m.ID <= txtTO.Value
so if the ID is not in M, it will not show, but I would like to show with a note.
how can I do that ?
You will need to use a LEFT JOIN clause and CASE where that missing row IS NULL to "Deleted".
Generate a number table of whatever size you want and then LEFT JOIN your query to that table.
with cte0 as (select 1 as c union all select 1),
cte1 as (select 1 as c from cte0 a, cte0 b),
cte2 as (select 1 as c from cte1 a, cte1 b),
cte3 as (select 1 as c from cte2 a, cte2 b),
cte4 as (select 1 as c from cte3 a, cte3 b),
nums as (select row_number() over (order by c) as n from cte4)
select t1.n, t2.*
from nums t1
left join (SELECT * from myMainIDTable m and some other inner join / left join for other informaiton
and in my WHERE clause I do WHERE m.ID >= txtFrom.Value and m.ID <= txtTO.Value) t2
where t1.n >= Value and t1.n <= Value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.