-1

I have 2 tables,

Users

  • username PK
  • nickname

Permissions

  • username FK to Users
  • permission_text

Given a username, Is it possible to return 1 row with all the permissions and user details?

I can think of two methods, Method 1 is to SELECT * FROM permissions WHERE username = <username> and do another query for SELECT * FROM users WHERE username = <username>.

Method 2 is to join both table and use the first row for user details and a loop to get permissions from the rest of the row.

I don't feel both method is very clean in term of relational database. What kind of query should I do so that I can get user details and array of permissions?

Zanko
  • 4,298
  • 4
  • 31
  • 54
  • 2
    For postgresql, use string_agg() function. See: http://stackoverflow.com/questions/15847173/concatenate-multiple-result-rows-of-one-column-into-one-group-by-another-column – bbb8989 Jan 29 '17 at 10:28

1 Answers1

0

You want to retrieve two datasets with very different cardinalities. The details about the user contain one (or zero if the user may not exist) rows. While the permissions this user has can potentially be a very large set.

One solution, as pointed out by bbb8989, is to concatenate the results into a string and to do some post-treatment in your client language. However I don't really like this solution as the values returned are not atomic.

Another solution is to do a join, but then all the fields about the user details will be duplicated and this is potentially a massive network bandwidth loss.

Hence my advice is to just use two distinct queries, as there is no clear advantage here to merge these queries; unless they are executed very, very often but then I'd rather suggest to merge the queries in the other direction; that is, retrieve the user details of all the interesting users in one pass.

Fabian Pijcke
  • 2,920
  • 25
  • 29