Sql subquery with inner join -

i've got these tables in database:

tourist - first table

    tourist_id - primary key extra_charge_id - foreign key  name...etc... 

extra_charges

    extra_charge_id - primmary key     excursion_id - foreign key      extra_charge_description 

tourist_extra_charges

tourist_extra_charge_id  extra_charge_id - foreign key  tourist_id - foreign key 

reservations

reservation_id - primary key  ..... 

tourist_reservations

tourist_reservation_id  reservation_id - foreign key  tourist_id - foreign key 

so here example: i've got reservation reservaton_id - 27 reservation has 2 tourists tourist_id - 86 , tourist_id - 87 tourist id 86 has charges extra_charge_id - 7 , , extra_charge_id - 11;

is possible make sql query , name , id of tourist , of charges

so output may this:

tourist_id : 86  name:john   extra_charge_id - 7 extra_charge_id - 11   tourist_id: 87  name:erika   extra-charge_id:10 

(here query made extra_charge_description of of tourists reservation_id = 27 don't know how change it. names above)

select extra_charges.extra_charge_description,tourist_extra_charges.tourist_id extra_charges           inner join tourist_extra_charges on extra_charges.extra_charge_id = tourist_extra_charges.extra_charge_id   inner join tourist_reservation on tourist_extra_charges.tourist_id = tourist_reservation.tourist_id           inner join  reservations on reservations.reservation_id = tourist_reservation.reservation_id           reservations.reservation_id=27 

your database schema not clear me, seems can link tourists tourist table charges in extra_charges table via tourist_extra_charges table this:

select  t.tourist_id         ,t.tourist_name         ,ec.extra_charge_id         ,ec.extra_charge_description    tourist t inner join tourist_extra_charges tec on t.tourist_id= tec.tourist_id inner join extra_charges ec on tec.extra_charge_id = ec.extra_charge_id; 

edit

if want able filter on reservation_id, you'll have join tables tourist_reservations , reservations well, this:

select  t.tourist_id         ,t.tourist_name         ,ec.extra_charge_id         ,ec.extra_charge_description    tourist t inner join tourist_extra_charges tec on t.tourist_id= tec.tourist_id inner join extra_charges ec on tec.extra_charge_id = ec.extra_charge_id inner join tourist_reservations tr on t.tourist_id = tr.tourist_id inner join reservations r on tr.reservation_id = r.reservation_id   r.reservation_id = 27; 

as database schema: please note field extra_charge_id not necessary in tourist table: link tourists charges via tourist_extra_charges table. can dangerous sanity of data make these kind of double connections.