CODEWITHSUNDEEP
mysqldatabase
Lk Thanvi
Lk Thanvi

Reputation: 87

Seeking optimised proper mysql query for multiple table joins

Hi developer/dba friends I have small issues for fetching details with mysql tables as following :

I have cid lets say cid=xxx

I want output(cid,ruser_id,dtoken,akey,prase) in one record using cid as key input

what mysql query I should perform that can also optimise this fetch with smooth less time on execution?

table structure is as following:

tbl_mk(cid,pro_id) -> pro_id primary key of tbl_pro

tbl_luser(cid,ruser_id) -> ruser_id primary key of tbl_ruser

tbl_ruser(id,dtoken)->id is primary key of this tbl_ruser where its referenced in tbl_luser as ruser_id

tbl_pro(id,akey)-> id is primary key of this tbl_pro which its referenced in tbl_mk as pro_id

tbl_app(akey,prase)

primary id/reference naming convention is i.e like if name of table is tbl_name then id referenced in other table for tbl_name is name_id. where id is primary key of tbl_name.

I know there are lot of mysql experts here so how to make it working with less efforts, fyi I am basically mobile app developer but there is time m working on some mysql stuffs for web apis needs :)

Thanks and I really appreciate and admire if some one can solve this problem for me.I did one query and getting details but seems its not proper way I need more efficient way thats why m posting here.

Waiting for some best reply with expected answer.

Upvotes: 0

Views: 33

Answers (1)

Mureinik
Mureinik

Reputation: 311796

Just join the tables. Assuming :cid is your input:

SELECT l.cid, l.ruser_id, r.dtoken, p.akey, prase
FROM   tbl_luser l
JOIN   tbl_ruser r ON l.ruser_id = r.id
JOIN   tbl_mk m ON l.cid = m.cid 
JOIN   tbl_pro p ON p.id = m.prod_id
JOIN   tbl_app a ON a.akey = p.akey
WHERE  l.cid = :cid

Upvotes: 1

Related Questions