Please tell me the mysql query

I have say 5 tables, user , t1, t2, t3, t4 user has a field called id. t1 to t4 all have a corresponding field called user_id, and a field called verification.

No开发者_开发问答w I need to distinctly(or uniquely) select the user from user table, whose id exists at least once in at least one table among t1 to t4,and where the verification field is true.

From you detail description it looks that you are near solution. The EXISTS and OR need to be used (assuming that TRUE replaced with 1):

SELECT id FROM User u WHERE
    EXISTS(SELECT 1 FROM t1 WHERE u.id = t1.user_id AND t1.verification =1) 
    OR EXISTS(SELECT 1 FROM t2 WHERE u.id = t2.user_id AND t2.verification =1 )
    OR EXISTS(SELECT 1 FROM t3 WHERE u.id = t3.user_id AND t3.verification =1 ) 
    OR EXISTS(SELECT 1 FROM t4 WHERE u.id = t4.user_id AND t4.verification =1 ) 

Tedious rather than difficult:

SELECT id FROM User JOIN t1 ON user.id = t1.user_id WHERE verification = TRUE
UNION
SELECT id FROM User JOIN t2 ON user.id = t2.user_id WHERE verification = TRUE
UNION
SELECT id FROM User JOIN t3 ON user.id = t3.user_id WHERE verification = TRUE
UNION
SELECT id FROM User JOIN t4 ON user.id = t4.user_id WHERE verification = TRUE

I'd use a UNION set on the t1-t4 tables to get the user_id in those, and use that result for a IN() clause on the user table, like this:

SELECT * FROM user
WHERE id IN (SELECT user_id FROM t1 WHERE verification = TRUE
             UNION
             SELECT user_id FROM t2 WHERE verification = TRUE
             UNION
             SELECT user_id FROM t3 WHERE verification = TRUE
             UNION
             SELECT user_id FROM t4 WHERE verification = TRUE)'

Just by the nature of how IN() works, you get distinct user table results as well.

Try this:

select
       distinct u.user
from
       user u
where
      exists (
      (select user_id from t1 where t1.user_id  = u.id and t1.verification = true)
   OR (select user_id from t2 where t2.user_id  = u.id and t2.verification = true)
   OR (select user_id from t3 where t3.user_id  = u.id and t3.verification = true)
   OR (select user_id from t4 where t4.user_id  = u.id and t4.verification = true)
   )