CODEWITHSUNDEEP
sqlsql-server
user1385222
user1385222

Reputation: 11

Best way to construct a query?

First I want to throw out I am still pretty new to SQL.

Consider you have a table with columns x and y, where both are positive integers.

Question 1:

What is the best way to find if a specified y value i exists for all x values?

Question 2:

What is the best way to find if a specified y value i exists for each x in a set of x values of size p?

I found a way where I do n number of self joins for the first case or p self joins for the second. But am curious of better ways to accomplish this.

An example table:

x,y
---
1,a
1,b
2,a
3,b
3,c

Upvotes: 1

Views: 69

Answers (1)

Bohemian
Bohemian

Reputation: 425033

Your suspicion is correct: Using a heap of joins is very inefficient.
Here's the efficient way:

Question 1: These two values will be the same if the premise is true

select 
    (select count(distinct x) from t1 where y = '1') as x_count_with_y,
    (select count(distinct x) from t1) as x_count

Question 2: This value will be the same as the number of x in the set if the premise is true

select count(distinct x)
from t1
where y = '1'
and x in (1,2,3,5,8)

Upvotes: 1

Related Questions