SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

ItBurns: Jul 24, 2007

I have three tables that I'm trying to join, really only two that I'm trying to join via an intermediary. The issue is that they use two different IDs for the same person. One table, Table_A, has ID_One. I'm linking this to a second table, Table_B, that has both ID_One and ID_Two, with the goal to then use that to finally link to Table_C. The only issue is that this is a one to many join when going from Table_A to Table_B, with Table_B having multiple rows of ID_One for every instance of ID_Two, but I really only care about a single row in Table_B because I'm just trying to get at the second ID in this table, ID_Two. The third table only has ID_Two, and has just one entry for every person.

My current solution looks like this...

code:

select
  ID_One,
  ID_Two
from (
  select
    ID_One,
    ID_Two,
    max( ID_Two ) over ( partition by ID_One ) as max_ID_Two
  from
    Table_B ) max_B
where
  max_B.ID_Two = max_B.max_ID_Two

This gives me an unduplicated table of ID_One and ID_Two that I can use join Table_A and Table_C. Is this the best, or even correct, approach? This is happening in Oracle, if there are better approaches for that system.

ItBurns fucked around with this message at 01:31 on Jun 11, 2015

# ¿ Jun 11, 2015 01:27

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 23:34

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'