Selecting person and their multiple letter ids?

Question

Trying to use sql to grab all users from a location "E.g. Boston" and grab all their letter ids. For example it should return something similar to this:

username  | letterID
Sam Smith | 5278665, 674943, 6549343
Jane Lenna| 8759385, 439533

In my Persons Table I have: userID, username, location, etc.
In my Letter Table I have: letterID, userID, title, description, etc.

What DBMS are you using (sql server, mysql, postgres, oracle, etc..)? — JNevill, May 31 '19 at 17:48
Circling back around to this one. The folks below have done a good job of writing out the join between your two tables and the intermediate result set that gets you *most* of the way there. Please check the duplicate I flagged using `STUFF()` to perform the string aggregation so you can get a comma delimited list of `letterid` for each `username`. The top solution there combined with the joins in the answers below will give you a 100% solution. — JNevill, May 31 '19 at 18:57

score 1 · Answer 1 · answered May 31 '19 at 17:51

You should simply use a join:

select a.username, b.letterID from persons a, letter b where a.userID = b.userID and a.location = 'Boston';

The result will be two columns with the username and the corresponding letter ID. A user would have as many rows in the result set as he has letters.

username  | letterID
Sam Smith | 5278665
Sam Smith | 674943
Sam Smith | 6549343
Jane Lenna| 8759385
Jane Lenna| 439533

If you see the same user name and letter ID more than once in the result than simply use a distinct in the query.

Iterate over the resulting rows and process the data anyway you like, including inserting them into a data structure you deem reasonable for your task.

This not only doesn't return the desired output but it is also using antiquated join syntax. Use ANSI-92 style joins, they have been around now for more than 25 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins And use meaningful aliases for joins or risk going insane. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 — Sean Lange, May 31 '19 at 18:26

score 1 · Answer 2 · answered May 31 '19 at 17:52

1

Try something like this:

SELECT
  p.username,
  l.letterID
FROM
  Persons p
  Join Letter l on l.userID = p.userID
WHERE
  p.location = "Boston"

answered May 31 '19 at 17:52

Jacob Goodwin

56
4

This is only a portion of the solution. This would return a row for every letter. They want a delimited list of LetterID values. – Sean Lange May 31 '19 at 18:29

score 1 · Answer 3 · answered May 31 '19 at 18:06

1

Try the following;

SELECT
  p.username,
  string_agg(l.letterID, ', ')
FROM
  Persons p
  Join Letter l on l.userID = p.userID
WHERE
  p.location = "Boston"
GROUP BY p.username

answered May 31 '19 at 18:06

Miguel Carreira

516
2
6

Selecting person and their multiple letter ids?

3 Answers3