Re: count question - Mailing list pgsql-sql

On 4/8/08, Craig Ringer <[email protected]> wrote:
> novice wrote:
>
> > Is it possible to write a query to produce:
> >
> >  meter_id | no_of_bays |   bay_id
> > ----------+------------+-----------
> >      5397 |          2 |   5397-01
> >      5397 |          2 |   5397-02
> >      5409 |          3 |   5409-01
> >      5409 |          3 |   5409-02
> >      5409 |          3 |   5409-03
>
> Sure. One way, not necessarily a particularly clever or efficient way,
> is to do a join with:
>
> generate_series(1, (select max(no_of_bays) from meter)) as i
>
> and use a WHERE clause to select for `i <= no_of_bays'

You have a problem, and it's bad design.

A bad design leads to these types of kludges.

Consider re-engineering your schema.

In any case, good luck.