mysql - Select multiple records from inner joined table by id -

trying inner join first time , thing going well, last step missing receive correct data database. sql query:

select roadtrip_tblgeneral.*, roadtrip_tblhotels.hotel, roadtrip_tbllocation.location, roadtrip_tbltransport.transport roadtrip_tblgeneral inner join roadtrip_tblhotels on roadtrip_tblgeneral.id = roadtrip_tblhotels.tripid inner join roadtrip_tbllocation on roadtrip_tblgeneral.id = roadtrip_tbllocation.tripid inner join roadtrip_tbltransport on roadtrip_tbltransport.id = roadtrip_tbltransport.tripid roadtrip_tblgeneral.tripcode = 'cb8v73x9' 

this response database: looks fine, 1 user can add multiple vehicles transport same id , want , not one. (eg. metro, car, bike, ...)

id  tripname      tripcode  hotel   location    transport         1   london        cb8v73x9  seaside london      metro 

i think might issue

on roadtrip_tbltransport.id = roadtrip_tbltransport.tripid 

you appear trying join 2 tables based on condition independent of first table being joined

looking @ pattern of previous lines did mean this:

on roadtrip_tblgeneral.id = roadtrip_tbltransport.tripid 

as @kickstart said, can move of transport modes 1 column changing select statement to

select roadtrip_tblgeneral.*, roadtrip_tblhotels.hotel, roadtrip_tbllocation.location, group_concat(roadtrip_tbltransport.transport) 

it may better handle @ application level if need access each transport type individually, group_concat() should fine if want string comma separated transport modes individual item