Find the list of X for which there is only one Y?

Shaitan00 · #1 (**permalink**) 06-22-08, 18:53

I am trying to find an SQL query that will return a list of items in column X that only appear once, or more precisly only have 1 corresponding Y (X is key). For example:

Table (R):
X | Y
-----
0 a
1 a
1 b
2 c
2 d
2 e

In this case, I would expect to only get back "0" because it only has one corresponding Y value (a), whereas "1" has two (a & b) and "2" has 3 (c & d & e) ... etc....

Any help would be greatly appreciated...
Thanks,

topcat2 · #2 (**permalink**) 06-22-08, 20:31

Assuming your table is called R and your columns are x and y:

Code:

select x from 
(select x, count(distinct y) nums from R group by x) y_count 
where nums = 1;

Shaitan00 · #3 (**permalink**) 06-22-08, 20:39

topcat2: thanks for the information. Quick question, do you know how to convert this to Relational Algebra?

Internet Services

Web Development

Digital Marketing

Consumer Tech