SQL join 3 tables

Question

I have a table 'images' which contains foreign keys from the tables 'event' and 'category'.

I have a function that displays all the events.

I'm able to select all events that are associated with pics in the images table, but i want to add another condition for category like:

select event from images where category is $category

Is there a way to join category to images aswell?

$query = "SELECT e.event
FROM images i
INNER JOIN event e 
ON e.eventID = i.eventID
WHERE event = '".$specificEvent."'";

Answer 1

You can do a 2nd inner join to categories. Guessing at column names:

SELECT e.event 
FROM images i INNER JOIN event e ON e.eventID = i.eventID 
INNER JOIN categories c ON c.categoryID = i.categoryID
WHERE event = 'specificEvent' AND category = 'someCategory'

Answer 2

You can add as many tables together with inner joins as you wish, just make sure you connect the correct fields

your example:

$query = "SELECT e.event FROM images i INNER JOIN event e ON 
          e.eventID = i.eventID WHERE event = '".$specificEvent."'";

Updated sample:

$query = "SELECT e.event FROM images i INNER JOIN event e ON 
         e.eventID = i.eventID 
         inner join Category c ON i.categoryid = c.categoryid 
         WHERE event = '".$specificEvent."'";

I havent tested it, but this is the format you use.

Answer 3

$query = "SELECT e.event FROM images i INNER JOIN event e ON e.eventID = i.eventID INNER JOIN category c ON c.categoryId = i.categoryId WHERE e.event = '".$specificEvent."' and c.categoryId = '".$specificCategoryId."'";