Query to get data from relating tables

Question

I have 3 tables.

galery_images:

• id | int (primari key)
• image | varchar (the link on the server)
• tartalom | longtext
• galery | int
• order | int

galery:

• id | int (primari key)
• name | varchar
• tartalom | longtext

1-n relation with the images

galery_category:

• id | int (primari key)
• name | varchar

n-1 relation with the galeries

I'm asking for an image, then I would like to fetch all the relevant data in a single query. I need all the columns, aka, 1 image, 1 galery, and all the categories.

I'm using codeigniter, so I would also like to know what I'm facing while retrieving the categories.

Answer 1

Assuming galery is linking column between galery_images and galery table and name is linking column between galery and galery_category table, you can use the following query.

SELECT gi.image, gi.tartalom, gi.galery, gi.order, g.tartalom, gc.name
FROM galery_images gi JOIN galery g ON gi.galery = g.name
JOIN galery_category gc ON g.name = gc.name
WHERE gi.image = ? //Other conditions

Please note that it will return multiple records for same image because of 1-many relationship.