CODEWITHSUNDEEP
mysqlselectschema
Andrew Rasmussen
Andrew Rasmussen

Reputation: 15099

Select multiple items associated with one item

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).

Upvotes: 0

Views: 2049

Answers (2)

xQbert
xQbert

Reputation: 35333

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

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

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

Upvotes: 3

Related Questions