I am building a basic photo album page, which features the selectec photo at the top, and the rest of the album thumbnails below. The structure of the URL is as follows:
The table structure has fields for event, filename and id
The event field can be attributed to multiple images from the same event. The event field can also be joined to the event table, which brings together separate races under the same event.
For the tumbnails below, I’d like to select all photos EXCEPT the one where the id is in the URL, for the event which the GET in the URL is associated.
I believe an inner join would be required, but I’m struggling to get it to work. Here is my query, which can select photos which aren’t the one being diplayed (from the URL) – but it can’t restrict to only showing other photos of the same event as the image being displayed.
SELECT distinct pg.`filename`, pg.`event`, pg.`id` from photogallery pg INNER JOIN photogallery pg2 on pg.`event` = pg2.`event` WHERE pg.`id` <> 302 and pg.`event` = pg2.`event`
Filename ¦¦ Event ¦¦ ID 1983-1.jpg ¦¦ 1983 ¦¦ 1 1983-2.jpg ¦¦ 1983 ¦¦ 2 1983-3.jpg ¦¦ 1983 ¦¦ 3 2001-1.jpg ¦¦ 2001 ¦¦ 4 2001-2.jpg ¦¦ 2001 ¦¦ 5 2001-3.jpg ¦¦ 2001 ¦¦ 6
I want the query to only show ID 2 and 3 if the GET is set to 1. But it’s showing IDs of 2, 3, 4, 5, 6.
You can do that with an inner self join on equal
event. But you need to filter for the sought
id in one of the instances and for non equality of the
id in both instances.
SELECT pg1.filename, pg1.event, pg1.id FROM photogallery pg1 INNER JOIN photogallery pg2 ON pg1.event = pg2.event WHERE pg2.id = 1 AND pg1.id <> pg2.id;