Edson Horacio Junior - 3 months ago 7x

SQL Question

I need to select table's *a* information along with a range of specific numbers, that is, for every *a.id* I want to show every number in the range, **using PostgreSQL 8.4**.

Let's suppose the range is the numbers 123, 175 and 192, this would be the result I want:

`range``a.id`

`123 ``1 `

`175 ``1 `

`192 ``1 `

`123 ``2 `

`175 ``2 `

`192 ``2 `

`123 ``3 `

`175 ``3 `

`192 ``3 `

I know I can achieve this using

`select range, a.id`

from a

inner join generate_series(1, 100, 1) range on true

But the thing is, I don't want to use

`generate_series`

Maybe something like this:

`select range, a.id`

from a

where range in (123, 175, 192)

group by range, a.id;

Answer

Given your comment:

For every a.id I want to show every number of the range

This creates what is called a `cartesian product`

. Here's one generic option using a `cross join`

with `union all`

:

```
select a.id, r.rng
from a cross join (
select 123 as rng
union all select 234
union all select 556
union all select 653
union all select 634) r
```

Source (Stackoverflow)

Comments