SQLTeam.com | Weblogs | Forums

Select DISTINCT returns list, how to JOIN to return only those that exist in ANOTHER TABLE?


#1

I have two tables: posts and questions.

My SQL query I use:
select DISTINCT question_id from posts WHERE user_id = 29 returns 4 id's:

NULL
0
80
79

NULL and 0 do not exist in 'question_id' field in questions table.

I want to return only those question_id's that exist the questions table:
80
79

How do I use a join to return the distinct value in posts table that must exist in questions table?


#2

Since you didn't reveal ddl, I'm guessing here:

select distinct
       a.question_id
  from posts as a
 where user_id=29
   and exists(select 1
                from questions as b
               where b.question_id=a.question_id
             )
;

#3

This worked great! Thank a millliiioooonnnnnn!