I am using StandardSQL in BigQuery and have 7 tables with 10 to 75 columns each and thousands of rows. For simplicity I will only use the relevant tables and columns for what I am trying to accomplish.

Table 1

| Item | Desc |
| --- | --- |
| 12341 | abcd |
| 23451 | bcda |
| 34561 | cdab |
| 45671 | dabc |

Table 2

| SubItem | Location | ON\_OFF | OnHand | OnOrder |
| --- | --- | --- | --- | --- |
| 12345 | 1 | ON | 3 | 5 |
| 12345 | 2 | ON | 4 | 2 |
| 12345 | 3 | ON | 2 | 4 |
| 12346 | 1 | ON | 7 | 7 |
| 12346 | 2 | ON | 1 | 4 |
| 12346 | 3 | ON | 8 | 7 |
| 23451 | 1 | OFF | 1 | 1 |
| 23451 | 2 | OFF | 3 | 2 |
| 34567 | 1 | ON | 6 | 0 |
| 34567 | 2 | ON | 1 | 5 |
| 34568 | 1 | ON | 2 | 0 |
| 34568 | 2 | ON | 3 | 10 |
| 45671 | 2 | ON | 5 | 1 |

Table 3

| Item | SubItem |
| --- | --- |
| 12341 | 12346 |
| 23451 | 23451 |
| 34561 | 34567 |
| 34561 | 34568 |

Current Result

| Item | Desc | ON\_OFF | OH | OO |
| --- | --- | --- | --- | --- |
| 12341 | abcd | ON | 9 | 11 |
| 12341 | abcd | ON | 16 | 18 |
| 23451 | bcda | OFF | 4 | 3 |
| 34561 | cdab | ON | 7 | 5 |
| 34561 | cdab | ON | 5 | 10 |
| 45671 | dabc | ON | 5 | 1 |

Desired Result

| Item | Desc | ON\_OFF | OH | OO |
| --- | --- | --- | --- | --- |
| 12341 | abcd | ON | 9 | 18 |
| 23451 | bcda | OFF | 4 | 3 |
| 34561 | cdab | ON | 5 | 5 |
| 45671 | dabc | ON | 5 | 1 |

I am looking for the Minimum OH and Minimum OO Value for each item and as in the case of Item 45671, that does not correspond with the same SubItem number.

Current code providing me with the Current Result table is:

```
Select 
Table1.Item,
Table1.Desc,
Table2.ON_OFF,
Table2.OH,
Table2.OO
From Table1
Left Join Table3
On Table1.Item = Table3.Item
Left Join 
    (Select SubItem, ON_OFF, Sum(OnHand) As OH, Sum(OnOrder) As OO
        From Table 2
        Group by 1,2)
ON Table3.SubItem = Table2.SubItem;

```

Looking for ideas as I am still fairly new to SQL and the current actual code ties 7 tables with various joins to build a final table with 45 columns and thousands of rows. I have looked at using RowNumber() and Partition By, but I am not sure where it would go. Was also thinking separating the OO and OH into two joins might help.

Any suggestions welcome! Thank you!