Select from within same MySql table based on id

Question

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:

/photos/photo.php?id=164

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`

Example dataset:

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.

So do you basically want to check that pg.id != pg2.id? and pg.id = the id from the URL — Nigel Ren
– Nigel Ren, Commented Mar 9, 2021 at 21:18

sticky bit · Accepted Answer · 2021-03-09 22:06:34Z

1

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;

answered Mar 9, 2021 at 22:06

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Abbott Over a year ago

Thank you, this is the solution. I appreciate this, I wasn't too far off. I'm inexperienced in MySQL, but I gave it a go and understand more now.

Collectives™ on Stack Overflow

Select from within same MySql table based on id

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related