CODEWITHSUNDEEP
mysqlsql
DS9
DS9

Reputation: 3033

Skip output of mysql query if user is blocked

I have two tables: question and block_user

I use following query to fetch question which are active:

SELECT 
    tq.question_id,tq.info,tq.owner_id
FROM
    question as tq
     ..... Other Join .......
     ..... Other Join .......
where
    tq.is_active = '1'

block_user table contain these fields: id,owner_id,question_id,block_user_id

now if quesNown owner blocked some user, then those user not able to see question in which they are blocked.

exa:

suppose table question contain following data:

question_id owner_id info

1 ....................... 1 ................ Hello
2 ....................... 1 ................ Hi
3 ....................... 1 ................ Welcome
4 ....................... 1 ................. Demo
5 ....................... 1 ................ Stack Over flow

suppose table block_user contain following data:

id owner_id question_id block_user_id

1...... 1................. 2...................... 2
2...... 1................. 4....................... 2

so, I am trying to do is if user 2 is tring to fetch question, then he is able to see question_id : 1,3,5
He is not able to see question_id : 2,4 , because in this question he is blocked.

I use following but it is not working:

SELECT 
    tq.question_id,tq.info,tq.owner_id
FROM
    question as tq
        join
    block_user as tbu ON tq.user_id not in (tbu.user_id)
    and tq.question_id not in (tbu.question_id)
    and '2' not in (tbu.block_user_id)
     ..... Other Join .......
     ..... Other Join .......
where
    tq.is_active = '1'

So how to achive this in one query?
Thanks in advance.

Upvotes: 1

Views: 95

Answers (2)

JCalcines
JCalcines

Reputation: 1286

This is the way I usually do this kind of query. I prefer to to a LEFT JOIN and discard those rows which match. Also you can use a subquery, but this usually obtain a faster result.

SELECT 
    tq.question_id,tq.info,tq.owner_id
FROM question as tq
    LEFT JOIN block_user as bu 
         ON tq.question_id = bu.question_id AND bu.block_user_id = '2'
     ..... Other Join .......
     ..... Other Join .......
WHERE tq.is_active = '1'
  AND bu.question_id IS NULL

Upvotes: 2

Bartłomiej Wach
Bartłomiej Wach

Reputation: 1986

try

SELECT 
    tq.question_id,tq.info,tq.owner_id
FROM
    question as tq
where
    tq.is_active = '1'
    and
    tq.question_id not in (SELECT bu.question_id from block_user bu where bu.block_user_id = {YOUR USER ID})

Upvotes: 1

Related Questions