I use SQL every day at work but I can't wrap my brain around a query I'm trying to write for a personal project.
I have written much, much more complex queries than this, but I just can't think of the algorithm I need here.
I have a set of "positions" say this table:
Position
C
1B
2B
3B
SS
MI
OF
P
and I have a set of Player Positions, say this table (simplifying and just having a small dataset to determine the algorithm I'll use in a true roto example):
player/position
a c
a 1B
b 1B
b 2B
c 2B
d 3B
e SS
e OF
I want to know what positions are *potentially* available.
Obviously P is available (no player is a P in this table). But so are SS and OF (because "e" can be at EITHER one so both are potentially free).
2B is NOT available (because "c" has it), but C and 1b are also not available, because with "c" at 2nd, "b" is forced to 1st and "a" is similarly forced to C.
Normally I'd be thinking of a query looking for all combinations (massive cross joining) and finding a combination where a particular column is null. But these are rows not columns and my brain is stuck in knots. I think there's an easy solution here I'm just missing it. Recursive CTE? Not sure.
I have written much, much more complex queries than this, but I just can't think of the algorithm I need here.
I have a set of "positions" say this table:
Position
C
1B
2B
3B
SS
MI
OF
P
and I have a set of Player Positions, say this table (simplifying and just having a small dataset to determine the algorithm I'll use in a true roto example):
player/position
a c
a 1B
b 1B
b 2B
c 2B
d 3B
e SS
e OF
I want to know what positions are *potentially* available.
Obviously P is available (no player is a P in this table). But so are SS and OF (because "e" can be at EITHER one so both are potentially free).
2B is NOT available (because "c" has it), but C and 1b are also not available, because with "c" at 2nd, "b" is forced to 1st and "a" is similarly forced to C.
Normally I'd be thinking of a query looking for all combinations (massive cross joining) and finding a combination where a particular column is null. But these are rows not columns and my brain is stuck in knots. I think there's an easy solution here I'm just missing it. Recursive CTE? Not sure.
Comment