Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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 ?
0
PhilippeRenaud
Asked:
PhilippeRenaud
1 Solution
 
lcohanDatabase AnalystCommented:
You will need to use a LEFT JOIN clause and CASE where that missing row IS NULL to "Deleted".
0
 
SharathData EngineerCommented:
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

Open in new window

1
 
Pawan KumarDatabase ExpertCommented:
Try..

CREATE TABLE SeqVal (VALUE INT not null CONSTRAINT PK_Val PRIMARY KEY);
go

INSERT INTO SeqVal VALUES(1);
INSERT INTO SeqVal VALUES(2);
INSERT INTO SeqVal VALUES(3);
INSERT INTO SeqVal VALUES(11);
INSERT INTO SeqVal VALUES(12);
INSERT INTO SeqVal VALUES(4)
INSERT INTO SeqVal VALUES(5);
INSERT INTO SeqVal VALUES(6);
INSERT INTO SeqVal VALUES(7);
INSERT INTO SeqVal VALUES(8);
GO

;WITH CTE AS
(
	SELECT v1 , v2  FROM 
	(
		SELECT Value+1 v1 ,LEAD(Value) OVER(ORDER BY Value)-1 v2
		FROM SeqVal
	)t
	WHERE v2>=v1
)
SELECT v1 , 'Deleted' Statex FROM CTE UNION SELECT v2 , 'Deleted' Statex  FROM CTE

Open in new window


O/p
-----------------------
v1      Statex
9      Deleted
10      Deleted


try this ---


;WITH CTE AS
(
	SELECT v1 , v2  FROM 
	(
		SELECT Value+1 v1 ,LEAD(Value) OVER(ORDER BY Value)-1 v2
		FROM (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) SeqVal
	)t
	WHERE v2>=v1
)
SELECT v1 , 'Deleted' Statex FROM CTE UNION SELECT v2 , 'Deleted' Statex  FROM CTE

Open in new window


Hope it helps!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now