4
Hi All,
I have a system that stores information about a person, name addresses and so on and I would like to return a single row for each person in an SQL statement. This is so it can be placed in a view making it easy to obtain all information for person x.
Since a person can have more than one address I thought I could build an array holding a row for each address with each row also been an array to store the fields.
The schema is a person table with their name and id in it and an addresses table that holds the address information:
Person:
int8 id,
text name
...
Addresses
int8 id,
int8 personid,
text line1,
text line2,
text town,
...
For example the result I would like is
1,Jon, Doe, { {1 street,some place,some city}, {2 drive,some other place, some other city} }
However I cannot figure out the SQL to achieve this.
For the SQL I had something like this:
SELECT p.id, p.name,
ARRAY[COALESCE(a.line1,''),
COALESCE(a.line2 ,''),
COALESCE(a.Town,''),
] AS "Address",
FROM People AS p
JOIN Addresses AS a ON a.PersonId=p.id;
(Coalesce since cannot have nulls in arrays :( )
Which works in getting a single address into an array. Of course if the person has more than one address I get two rows returned for person x.
Attempting to re-work the SQL I could not get anything to run, the closest been:
SELECT p.id, p.name,
ARRAY[ ( SELECT COALESCE(a.line1,''),
COALESCE(a.line2 ,''),
COALESCE(a.Town,''),
FROM Addresses AS a ON a.PersonId=p.Id ) ] AS Addresses
FROM People AS p
Which obviously fails if there is more than one address.
Suggestions or ideas would be welcome.
Thanks
