Phil Phil - 1 year ago 69
SQL Question

Returning all data in one row

In my Postgresql Database, I have multiple reference data tables.

- Gender(id:Serial, name:varchar)
- Sexuality(id:Serial, name:varchar)
- Location(id:Serial,name:varchar)

To retrieve all of the information in all of the tables I am doing 3 separate select statements.

- "SELECT name from Gender;
- "SELECT name from Sexuality;
- "SELECT name from Location;

How can I make this into one call so that it returns one row like this:

ReferenceData(allGenders:varchar[], allSexualities:varchar[], allLocations:varchar[])

I would like to be able to be able to do something like this client side -->

var genders = results.row[0].allGenders;
for gender in genders {

Answer Source

You can use the array_agg aggragate function to combine values from multiple rows into a single array, e.g.

SELECT array_agg(name) FROM Gender

will return all genders as a single array. If you want all the information from all 3 tables at once, you can do it like that:

    (SELECT array_agg(name) FROM Gender),
    (SELECT array_agg(name) FROM Sexuality),
    (SELECT array_agg(name) FROM Location);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download