CODEWITHSUNDEEP
databaseperformancesqlite
handle
handle

Reputation: 6339

Database structure, query vs iteration

(Basic question that probably is a duplicate, but I don't know what to search for, so feel free to edit this question with the proper database terms)

How would one retrieve groups of rows (family members) from a database table based on columns (last and first names) efficiently? E.g. from this

last  first ...
doe   john  ...
doe   jane  ...
smith jimmy ...
smith ted   ...
smith anna  ...

to something like this (additional data omitted)

doe : [{first:john, ...}, {jane}],
smith: [{jimmy}, {ted}, {anna}]

Does this require retrieving the common data (last name) with distinctor group by first and then iterating with additional queries (where last="smith") for each name?
I'd think that that naive approach likely is inefficient and there are better solutions.

Upvotes: 1

Views: 38

Answers (1)

forpas
forpas

Reputation: 164154

You need GROUP_CONCAT() aggregate function:

SELECT last, GROUP_CONCAT(first) first
FROM tablename
GROUP BY last

or JSON_GROUP_ARRAY():

SELECT last, JSON_GROUP_ARRAY(first) first
FROM tablename
GROUP BY last

See the demo.

Upvotes: 1

Related Questions