Select multiple items associated with one item

Question

I feel like there is a better way to do this. I'm mostly just asking if what I'm doing is right, or if there's a much better way.

Observe these tables:

Users
id | name
---+---------
 1 | Andrew
 2 | Greg

Images
id | userid | url
---+--------+-------
 1 |   1    |  img/foo.jpg
 2 |   2    |  img/bar.jpg
 3 |   2    |  img/baz.jpg
 4 |   1    |  img/qux.jpg

If the Users table had a single foreign key that references Images, each user could only be associated with a single image. However, I'd like to allow each user to be associated with any number of images, hence putting a Users foreign key in Images instead.

I understand how to use JOIN if user has a single foreign key to data stored in another table. However, in this case, there is another table with a number of foreign keys associated with the user I'm interested in. How would I construct a SELECT query that will retrieve all of the images associated with the user in addition to all of the data associated with the user (user type, phone number, blah blah blah [I know how to do this already]) in one SELECT query?

Or, is there a better way to do this?

Edit 1:

For example, if you select the user with id = 1 then it would return the first row of Users and the first and last row of Images (because those rows have userid = 1).

what should your desired result look like? can u give some example? — helle, Dec 27 '11 at 10:04
so you got two rows in your result table (in the case of usr 1)? — helle, Dec 27 '11 at 10:09
Exactly. It would include all of the rows in Images where userid = 1. But I also want to include the other user data such as their name in a single row. Is that possible? — Andrew Rasmussen, Dec 27 '11 at 10:11

score 3 · Accepted Answer · answered Dec 27 '11 at 10:30

3

If you do not want multiple rows with identical (user) data you can either group per user and use the GROUP_CONCAT() function to gather all the urls in one column:

SELECT u.*
     , GROUP_CONCAT(i.URL)
FROM users u
  LEFT JOIN Images i 
    ON u.ID = i.userID
WHERE u.ID = 1
GROUP BY u.ID

or use 2 queries:

SELECT u.*
FROM users u
WHERE u.ID = 1

SELECT i.URL
FROM Images i 
WHERE i.userID = 1

answered Dec 27 '11 at 10:30

ypercubeᵀᴹ

113,259
19
174
235

Design-wise, would you suggest A) returning csv, B) doing two separate queries, or C) storing csv in the mysql db itself? Assuming I might have to add/remove images later... I know that I can make this work using my schema and your answer, but what I'm asking is would it be considered good style/design to do it this way? – Andrew Rasmussen Dec 27 '11 at 10:35
Your schema is ok. I would use B. Using A means the SQL engine has to concat the urls which you'll probably separate again in the web server. Using C should be a definitive no: http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad – ypercubeᵀᴹ Dec 27 '11 at 10:38

xQbert · Answer 2 · 2011-12-27T10:44:03.257

1

So... If it's a 1-1 or a 1-M the join is the same. Where variances come into play is when you want all of one where values don't exist in the other or you want all of both regardless if matches are found. One of my favorite sites explaining sql join logic is coding horror

Select U.Name, I.URL
FROM users U 
INNER JOIN Images I ON U.ID = I.userID
Where U.ID = 1

would return

U.Name  I.URL
Andrew  img/foo.jpg
Andrew  img/Qux.jpg

EDIT So what you are after is A

U.Name  I.URL
Andrew  img/foo.jpg
        img/Qux.jpg

OR B?

U.Name  I.URL
Andrew  img/foo.jp, img/Qux.jpg

edited Dec 27 '11 at 10:44

answered Dec 27 '11 at 10:25

xQbert

34,733
2
41
62

1

But then if there are a hundred images associated with Andrew, this will return "Andrew" a hundred times. That seems very wasteful since I only need it once. Is there a way to get all of the image urls and the name only a single time? Possibly requiring modification of my schema? Hopefully only in one query? :) – Andrew Rasmussen Dec 27 '11 at 10:27
No, not without combining all the urls into one string, e.g. comma separated (see @ypercube's answer) – mathematical.coffee Dec 27 '11 at 10:32
@arasmussen IMO it's not wasteful. If you already know Andrew, which you do since you know to search on 1, then simply return just the I.URL field. If you didn't know Andrew at this point, there is a modest amount of overhead for each instance of Andrew; but that's simply how the logic works. Or as previously stated, use ypercube's answer to group_Concat the restults, but then you have to seperate them out. Simply put data is stored in normalized fashion, queries return data in a denormalized fashion to avoid confusion on returned data. – xQbert Dec 27 '11 at 10:39

Select multiple items associated with one item

2 Answers2