Table: University
| university | state |
|---|---|
| UCLA | CA |
| CUNY | NY |
| UCB | CA |
| USC | CA |
| UM | MI |
| NYU | NY |
Table: UniColor
| university | color |
|---|---|
| UCLA | RED |
| UNY | BLUE |
| UCB | RED |
| USC | ORANGE |
| UM | CYAN |
| NYU | BLUE |
Desired Result:
| university1 | university2 | state |
|---|---|---|
| UCB | UCLA | CA |
| CUNY | NYU | CA |
I'm trying to get pairings of universities that are located in the same place and have same symbol color? My initial approach is
SELECT t0.name, t1.name, co.color
FROM University AS t0,
University AS t1,
UniColor AS co
WHERE t0.university = co.university
AND t0.state = t1.state
GROUP BY co.color
HAVING COUNT(c.color) > 1
but it's clearly not working. Could anyone please help me out?
Thank you!
CodePudding user response：
with uni_data as (
SELECT u.university, u.state, c.color
FROM university u
INNER JOIN UniColor c on u.university = c.university
)
SELECT u1.university, u2.university, u1.state
FROM uni_data u1
INNER JOIN uni_data u2 ON
u1.state = u2.state AND
u1.color = u2.color AND
u1.university < u2.university -- ensures only a,b not b,a as well