SQL Left join same table but with condition possible?

I have a main Table

|-------------|------------|  | Procedure   | Modifier   |  |:------------|:-----------|  |     AA      |     00     |  |     AA      |     21     |  |     AA      |     26     |  |-------------|------------|  

and a rates Table

|-------------|------------|-------|  | Procedure   | Modifier   | Rate  |  |:------------|:-----------|-------|  |     AA      |     00     | $10   |  |     AA      |     21     | $20   |  |     AA      |     QA     | $30   |  |-------------|------------|-------|  

I am looking to left join the rate table to main table ON procedure AND modifier, to find the rate.

However, if there is no matching procedure AND modifier, then I want to join by procedure and modifier 00 to get the rate of $10.

End result to look like this, |

-------------|------------|------|  | Procedure   | Modifier   | Rate |  |:------------|:-----------|------|  |     AA      |     00     |$10   |  |     AA      |     21     |$20   |  |     AA      |     26     |$10   |  |-------------|------------|------|  

NOT

|-------------|------------|------|  | Procedure   | Modifier   | Rate |  |:------------|:-----------|------|  |     AA      |     00     |$10   |  |     AA      |     21     |$20   |  |     AA      |     26     |null  |  |-------------|------------|------|  

I suppose 'worse' case is to do this separately and split into two statements. Where my first left join would be by proc and modifier then later in my second statement just by procedure? Is there a way to do this within one statement?

https://stackoverflow.com/questions/66810445/sql-left-join-same-table-but-with-condition-possible March 26, 2021 at 11:01AM