user7082 user7082 - 1 month ago 7
SQL Question

Sql query to print values starting from column A till column B

New to SQL so looking for help

I'm trying to write a query which would print values starting from column A till the column B excluding the value present in column 'ANS' of second table.

Like here are the two tables X and Y

Table1
A FROM TO
a 6 9
b 3 6
c 0 3
d 2 3

Table2
A ANS
a 7
b 5
c 1


And I want the output as

A ANS
a 6
a 8
a 9
b 3
b 4
b 6
c 0
c 2
c 3
d 2
d 3


I've tried to write something like this but it doesn't work

WITH y(n) AS
(SELECT 1 AS n
FROM dual
UNION ALL
SELECT n + 1 AS n
FROM y, table1 T
WHERE n <= T.TO AND n>= T.FROM )
SELECT * FROM y;


Which prints 5000+ rows (that's why I am not attaching output)
Thanks in advance

vkp vkp
Answer

After you get all the numbers between from and to with a recursive cte, left join on the generated table and get only those numbers which don't exist in table2 using not exists.

--Get the maximum value of `to` column and generate all numbers between 0 and that value
WITH maxto(maxt) as (SELECT MAX(TO) FROM TABLE1)
,y(n) AS 
(SELECT 0 AS n FROM dual
 UNION ALL
 SELECT n + 1 AS n FROM y WHERE n < (SELECT maxt FROM maxto)) 
SELECT * FROM
(SELECT t1.a, y.n
 FROM y
 LEFT JOIN table1 t1 on y.n between t1.from and t1.to 
 WHERE t1.a IS NOT NULL) x
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE x.a = a and x.n = ans)
ORDER BY 1,2

Sample demo

Comments