[thelist] MySQL: from lookup tables to flat table

[Brooking, John]

> From: kasimir-k <evolt at kasimir-k.fi>
>
> I have tables
> persons - personId, name
> fields - fieldId, fieldName
> types - typeId, typeName
> persons_x_fields - personId, fieldId
> persons_x_types - personId, typeId
> 
> A person may belong to 0 or more fields and be of 0 or more types.
> 
> What kind of query would give a table like this:
> personId, name, [field1, field2, ...], [type1, type2, ...]
> 
> i.e. field and type columns are comma separated lists which can also
be 
> empty.

I don't see how this can be done in a pure SQL query, at least without
putting an upper limit on the number of fields and types that can be
associated, and maybe not then. I would just resort to a procedural
language, either within the database if your DB has it (such as PL/SQL
in Oracle), or in application code.

- John
-- 

This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.