Problem making 1 big query from lots of small ones

I'm having a problem which I easily solve with php and some queries, but I wanted to make one big query (at least if it's faster which I believe it should be).

So I have 3 tables and let simplify them some:

topic which has 3 columns

• id
• user_id
• visibility

access which has 2 columns

• topic_id
• user_id

friendship which has 2 columns

• user_id
• friend_id

What I want to do is if a user (we call him watch_id) is trying to watch this topic I want to return the topic if he is allowed to watch or if he is not, return nothing. He is allowed to watch if any of these is true:

• watch_id == user_id
• visibility == 3
• visiblity == 2 && the friendship table returns a row when friendship.user_id = topic.user_id && friendship.friend_id == watch_id
• visibility == 1 && the access table returns a row when access.topic_id = topic.id && acce开发者_StackOverflowss.user_id == watch_id

As you can see it's not very hard to do with php and a bunch of queries but in sql only I can't figure it out. I've been trying joins, case and stuff but the logic never adds up. :(

So can you guys help me? Or am I stuck with php and lots of queries?

Edit: Hmm, looks like I wasn't making myself perfectly clear, my bad! He is allowed to watch if any of those requirements is true. Since this is the case I go with left join answer which was easiest to extend. Thanks all!

You should be able to make left joins against the friendship and access tables, so that the joins themselves doesn't limit the result:

select something
from topic
left join friendship on friendship.user_id = topic.user_id && friendship.friend_id == watch_id
left join access on access.topic_id = topic.id && access.user_id == watch_id
where
  watch_id == topic.user_id or
  topic.visibility == 3 or
  (topic.visiblity == 2 and friendship.user_id is not null) or
  (visibility == 1 && access.topic_id is not null)

This might be possible with one query but the easiest would be to use a UNION to combine the results of your different requirements.

SELECT t.*
FROM   topic AS t
WHERE  t.user_id = @watch_id AND t.visibility = 3
UNION ALL
SELECT t.*
FROM   topic AS t
       INNER JOIN friendship AS f ON f.user_id = t.user_id
WHERE  f.friend_id_id = @watch_id AND t.visibility = 2
UNION ALL
SELECT t.*
FROM   topic AS t
       INNER JOIN access AS a ON a.topic_id = t.id
WHERE  a.user_id = @watch_id AND t.visibility = 1

Try subqueries:

select topic.* from topic
where (topic.user_id = watch_id and visibility=3) or 
      (topic.user_id = watch_id and visibility=2 and (select count(*) from friendship where friendship.user_id = topic.user_id and friendship_id = watch_id) > 0) or
      (topic.user_id = watch_id and visibility=1 and (select count(*) from access where access.topic_id = topic.id and access.user_id = watch_id) > 0)

This solution allows for a single topic even if there are multiple friendship or access entries. Looking at your question, I made the assumption that topic.user_id must equal watch_id for all three, but I could be wrong in that.

If, however, there is only ever a one-to-one-to-one relationship between the three tables, then left joins are the way to go.

If you have three separate queries that return what you need then you could consider performing a union on the three result sets to get back the topics you want.

Basically the following should get you back the list of all topics a user can watch.

select t.topic_id
from   topic as t
where  t.user_id = watch_id
and    t.visibility = 3
  union
select t.topic_id
from   topic as t
inner join friendship as f
on     f.user_id = t.user_id
where  f.friend_id = watch_id
and    t.visibility = 2
  union
select t.topic_id
from   topic as t
inner join access as a
on     a.topic_id = t.id
where  a.user_id = watch_id
and    t.visibility = 1

You should be able to use that to get you what you need.

If you need a procedure or function that can confirm if a given topic_id can be watched then you could look to check to see if a topic_id is contained within the above result set