CODEWITHSUNDEEP
sql
Weibin Yu
Weibin Yu

Reputation: 13

SQL Select x from a set of value

Hi guys i have a problem about how to select value from a set of value in SQL and my code look like this:

Select distinct subreddit from reddit
where author = (select distinct author from reddit where link_id = 't3_j56j2');

Code in the () return more than one author but when i run this query it only gets the value of one author. What should i do to get value from all the authors () part returns?

Upvotes: 1

Views: 928

Answers (3)

Marcus Höglund
Marcus Höglund

Reputation: 16801

I would recommend to using EXIST instead of IN statement.

The IN statement will execute the full query inside the IN-statement. In the below example the (select author from reddit where link_id = 't3_j56j2') will be executed and then compared to the value in author

Select subreddit from reddit
where author IN (select author from reddit where link_id = 't3_j56j2');

If you use EXISTS instead:

Select subreddit from reddit t
where EXISTS(select rownum from reddit where link_id = 't3_j56j2' and author = t.author);

The query in exist will stop executing at the first match and will be the better option for performance.

Upvotes: 0

ilhan kaya
ilhan kaya

Reputation: 51

Use

Select distinct T.subreddit from reddit T where EXISTS (select author from reddit where link_id = 't3_j56j2'and author = T.author);

Upvotes: 1

jarlh
jarlh

Reputation: 44766

I guess you simply want IN instead:

Select distinct subreddit from reddit
where author IN (select author from reddit where link_id = 't3_j56j2');

Note: No need for distinct in the sub-query.

Upvotes: 0

Related Questions