Lookup Table Query

Databases
1

I have 3 tables in my database: memorials, photos and memorial_photos.

The “memorials” table stores the main data, the “photos” table stores photo id and photo name and “memorial_photos” is a lookup table that stores the memorial id and photo id for each photo in the “photos” table.

I am displaying the memorial data with no problem, my question is what is the best way to go about displaying a corresponding photo for each record? Also, what is the best way to use a lookup table? And finally, am I going about this correctly or should I just store the memorial id in the “photo” table and drop the “memorial_photos” table all together?

Any help is greatly appreciated. I want to learn how to do this the right way but I can’t seem to find anything that points me in the right direction. Thanks.

2

the answer in one word: joins :slight_smile:

on your other question, if you store the memorial_id in the photos table, that implies that a particular photo will only ever be related to a single memorial

3

Ok, so I have written my SELECT statement using LEFT JOINs.

select * from (memorials left join memorial_photos on memorials.id = memorial_photos.memorialid) left join photos on memorial_photos.photoid = photos.id;

Now I have a new problem. When I am displaying my list of memorials I have duplicates because there are multiple photos associated with each record and my new query is returning a record for each photo associated with the memorial. I just want to show each memorial once and display only one corresponding photo for each.

I hope I am making sense. Thanks.

4

which photo?

5

I just want to display the first photo associated with the each memorial.

6

sorry, but there is no sequence of rows in a database table, there is no such thing as “first”

you would have to specify which photo you want based on the value in some column

e.g. photo with longest name, photo with earliest postdate, largest photo dimensions, or something like that