Can I use MySQL to join two tables based on relationships with a third table?

Question

Let's say you have three tables named Item, Event, and Seat, constructed as such:

Item
Id (int)
Event_id (int)
Section (int)

Event
Id (int)
Venue_id (int)
Configuration (int)

Seat
Id (int)
Venue_id (int)
Configuration (int)
Section (int)
Face_value (int)

I'm trying to construct a MySQL query that pulls all entries from the Item table and includes the Face Value for each item. In order to do this, the query must:

1. Use the Item.Event_id value and match it to Event.Id
2. For that entry in the Event table, it must take the Event.Venue_id and Event.Configuration and find an entry in the Seat table that has the same values for both AND also has the same value for Section as Item.Section. It must then return Face_value.

I'm having a lot of trouble constructing this because of the way it combines info from all three tables. Any idea on how to approach it? Or is this impossible to do in SQL?

Answer 1

SELECT `Item`.*, `Seat`.`Face_value`
FROM `Item`
JOIN `Event` ON `Event`.`Id` = `Item`.`Event_id`
JOIN `Seat` USING (`Venue_id`, `Configuration`)
WHERE `Seat`.`Section` = `Item`.`Section`

Answer 2

Same as chaos, but I prefer this syntax :-)

select s.Section, s.Configuration, s.Face_value
from   Item i
       inner join Event e
       on e.id = i.Event_id
       inner join Seat s
       on s.Venue_id = e.Venue_id
    and
        s.Configuration = e.Configuration
    and
        s.Section = i.Section