user7082 - 9 months ago 40

SQL Question

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

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
```

Source (Stackoverflow)