Re: scaleable design for multiple value tuple

Daniel,

> > in the report output i do not want to see three records for the
> > above case (one for each engineer). i just want to see a view of
> > the case but including which engineers belong to that case.
>
> Shouldn't be too hard, in most cases ;-). I'd first try doing it on
> the client side, but then I'm more of a perl programmer than a SQL
> programmer. There is probably a good way to do this in SQL
> (subquerys maybe? Or would you have to use the string concatenation
> operator? Hmmm...), but I'd have to test it and I don't have the
> gumption to come up with good test data at the moment. Keep
> prodding, and you'll probably get someone to come up with something.

He can use custom aggregates to concatenate the 3 engineers, e.g.:

SELECT case_id, case_name, comma_cat(engineer)
FROM cases JOIN case_engineers USING (case_id)
GROUP BY case_id, case_name;

where comma_cat is a concatenation aggregate per the example in CREATE
AGGREGATE.

--
Josh Berkus
Aglio Database Solutions
San Francisco