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