Results 1 to 23 of 23
Apr 9, 2013, 10:37 #1
- Join Date
- Apr 2013
- 0 Post(s)
- 0 Thread(s)
Selecting one through many in a many-to-many relationship
The situations is this (somewhat simplified):
I have a MySQL database that contains data about photographs. One table (photos) contains basic data such as year, place etc. Then I have another table (names) that contains names of people in the pictures. And finally there's a table (pic_name) that connects pictures with people, linking photoid with nameid corresponding to photos.id and names.id respectively.
Let's say that the picture with id 1 (photos.id = 1) is a photo of three people called John, Lisa and Esmeralda having the IDs 1, 2 and 3 in the names table (as well as the names encoded in the first_name column). Then pic_name has three rows connecting photoid = 1 with nameid = 1, 2 and 3.
Now to my question: How do I construct a query that retrieves some specific info from the photos table, for example place or year, for all photos that contain both John and Lisa (not just either one of them)? That is, how do I get a list of where or when all my pictures containing both John and Lisa (and possibly others such as Esmeralda, but that's irrelevant) were shot?
Intuitively, it seems like a fairly simple question, but I can't for my life come up with a query that produces the desired result. Any help would be highly appreciated.