Regarding JOIN query in Mysql

Here is my database structure:

payment option:
    mshiptype_id
    paymopt_Id 

membertopaymentoption:
    member_Id
    paymopt_Id

mshiptypes
    mshiptype_name
    mshiptype_id
    timetable_id 

timetables
    timetable_id
    timetable_Name 

timeslots
    timeslot_id
    timeslot_name

timeslo开发者_如何学JAVAttotimetables
    timeslot_id
    timetable_id

I want to get the mshiptype_name and timeslot_name and timetable_name for single member

by using above tables

would any one give suggestions on join query......in mysql

SELECT mshiptype_name, timeslot_name, timetable_name
FROM mshiptypes ST
INNER JOIN paymentoption PO ON ST.mshiptype_id = PO.mshiptype_id
INNER JOIN membertopaymentoption  MPO ON PO.paymopt_Id = MPO.paymopt_Id
INNER JOIN timetables TT ON ST.timetable_id = TT.timetable_id
INNER JOIN timeslottotimetables TTT ON TT.timetable_id = TTT.timetable_id
INNER JOIN timeslotss TS ON TTT.timeslot_id = TS.timeslot_id
WHERE  MPO.member_id = <members_id>

I think this is what you want:

SELECT mshiptypes.mshiptype_name, timeslots.timeslot_name, timetables.timetable_Name
FROM membertopaymentoption
INNER JOIN paymentoption ON membertopaymentoption.member_Id=paymentoption.paymopt_Id
INNER JOIN mshiptypes ON paymentoption.mshiptype_id=mshiptypes.mshiptype_id
INNER JOIN timetables ON mshiptypes=timetable_id=timetables.timetable_id
INNER JOIN timesslottotimetables ON timetables.timetable_id=timeslottotimetables.timeslot_id
INNER JOIN timeslots ON timeslottotimetables.timeslot_id=timeslots.timeslot_id
WHERE membertopaymentoption.member_Id=$id

Note: replace $id in the WHERE clause with the actual memeber id

The trick is to identify fields common to two different tables, and try to find a path linking all the fields you want.

select
    mtpo.member_Id,
    mt.mshiptype_name,
    ts.timeslot_name,
    tt.timetable_name
from
    payment_option po
    left join membertopaymentoption mtpo on po.paymopt_Id = mtpo.paymopt_Id
    left join mshiptypes mt on mt.mshiptype_id = po.mshiptype_id
    left join timetables tt on tt.timetable_id= mt.timetable_id
    left join timeslottotimetables tstt on tstt.timetable_id = tt.timetable_id
    left join timeslots ts on ts.timeslot_id = tstt.timeslot_id
where
    member_Id = 1  -- change this

Also, I suggest tidying up the names of your fields and tables before it gets too late. They are quite messy!

select mt. mshiptype_name, ts. timeslot_name, tt. timetable_Name
from  membertopaymentoption mtp
inner join payment p on mtp. paymopt_Id = p.paymopt_Id
inner join mshiptypes mt on p.mshiptype_id = m.mshiptype_id
inner join timetables tt on mt.timetable_id = tt. timetable_id
inner join timeslottotimetables tstt on tt timetable_id = tstt. timetable_id
inner join timeslots ts on tstt. timeslot_id = ts. timetable_id
where mtp. member_Id = @memID //will be provided

select
    mshiptype_name,
    timeslot_name,
    timetable_name
from
    paymentoption
    natural join membertopaymentoption
    natural join mshiptypes
    natural join timetables        
    natural join timeslottotimetables
    natural join timeslots
where
    member_Id = 1