mysql - Facing problems in multiple table joins -

there 4 tables.

• items ( item_id, item_name, item_owner)
• groups ( grp_id, grp_name, grp_owner)
• users (grp_id, usr_ref)
• share (item_id, grp_id)

my objective list of items item_owner = user_id ( 123 ) or user_id belongs group item shared.

a basic query implementation retrieve items shared group particular user_id belongs

select i.item_id items left outer join share on share.item_id = i.item_id left outer join users on users.grp_id = share.grp_id left outer join groups on groups.grp_id = share.grp_id users.usr_ref = user_id 

and include other elements of user_id owner, did

select * items owner = user_id or item_id in (  select i.item_id items left outer join share on share.item_id = i.item_id left outer join users on users.grp_id = share.grp_id left outer join groups on groups.grp_id = share.grp_id users.usr_ref = user_id ) 

which suppose bad implementation item_id needs searched everytime in array obtained joins. how can improve sql statement.

also there other way in can redesign table structure can implement query in other way ?

thanx in advance.

you need inner join in case because need item has connection on tables. current query uses left join why item has not associated on user shown on list. give try,

select  distinct a.*    items         inner join `share` b             on a.item_id = b.item_id         inner join groups c             on b.grp_id = c.grp_id         inner join users d             on c.grp_id = d.grp_id   d.usr_ref = user_id 

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins