CODEWITHSUNDEEP
mysqlsqljoin
Enihr
Enihr

Reputation: 409

Facing problems in multiple table joins

There are 4 tables.

My objective is to get a list of all those items where item_owner = user_id ( say 123 ) or user_id belongs to a group with which the item is shared.

A basic query implementation to retrieve all items shared with a group to which a particular user_id belongs would be 

select i.item_id from items i
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
where users.usr_ref = user_id

And to include all other elements of which user_id is the owner, i did something like 

select * from items where owner = user_id or item_id in ( 
select i.item_id from items i
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
where users.usr_ref = user_id )

which i suppose is a very bad implementation as the item_id needs to be searched everytime in the array obtained from the joins. How can i improve my sql statement.

Also is there any other way in which i can redesign my table structure so that i can implement the query in some other way ?

Thanx in advance.

Upvotes: 3

Views: 126

Answers (3)

Gonzalo.-
Gonzalo.-

Reputation: 12672

I think this will work.

select i.item_id from items i
inner join share on share.item_id = i.item_id
inner join users on users.grp_id = share.grp_id
inner join groups on groups.grp_id = share.grp_id
where (users.usr_ref = @user_id or @user_id is null) and  (i.item_id = @item_id or @item_id is null)

I'm guessing that you will always pass one paramether or the other one, not both, so the other one (the non-passed) will be null

also, why are you using left join? use inner instead, because you don't want null fields

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Perhaps I'm not understanding your question, but can you not just use OR with your first query: 

select i.item_id from items i
   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
where i.item_owner = @user_id or users.usr_ref = @user_id

Upvotes: 0

John Woo
John Woo

Reputation: 263713

You need INNER JOIN in this case because you need to get an item that has connection on all tables. Your current query uses LEFT JOIN that is why even an item that has not associated on any user will be shown on the list. Give this a try,

SELECT  DISTINCT a.*
FROM    items a
        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
WHERE   d.usr_ref = user_ID

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 2

Related Questions