Joining Two Subqueries That Contain A Subquery

Question

Still new to SQL. I have looked around for an answer on this, and the examples that I have found, I have mirrored my query to look like the examples, and this query still isn't working. All Im trying to do is join two subqueries, but both of these subqueries contain a subquery inside. I'm receiving the following error:

Msg 156, Level 15, State 1, Line 26 Incorrect syntax near the keyword 'On'.

This is the query, please assist, thank you.

Select *
From 
(
Select * From(
Select x.ID, x.Date, x.USOHist, x.OVXHist,
Abs(Cast((((x.USOHist / NullIf((y.USOHist),0))-1)*100) as Decimal(10,2)))         AS '%USOH',
Abs(Cast((((x.OVXHist / NullIf((y.OVXHist),0))-1)*100) as Decimal(10,2)))   AS '%OVXH'
From (Select a.Date as aDate, Max(b.Date) As aPrevDate From USO_OVX_Hist a      Inner Join USO_OVX_Hist b on a.Date > b.Date
Group By a.Date) Sub1
Inner Join USO_OVX_Hist x on Sub1.aDate = x.Date
Inner Join USO_OVX_Hist y on Sub1.aPrevDate = y.Date
) Sub2

Inner Join 
(
Select * From(
Select z.ID, z.ID2, z.Date, z.USO as USOP, z.OVX as OVXP,
Cast(((z.USO / NullIf((q.USO),0)- 1) * 100) as Decimal(10,2)) AS '%USOP',
Cast(((z.OVX / NullIf((q.OVX),0)- 1) * 100) as Decimal(10,2)) AS '%OVXP'
From (Select c.Date as cDate, Max(d.Date) As cPrevDate From USO_OVX_Price     c Inner Join USO_OVX_Price d on c.Date > d.Date
Group By c.Date) Sub3
Inner Join USO_OVX_Price z on Sub3.cDate = z.Date
Inner Join USO_OVX_Price q on Sub3.cPrevDate = q.Date
) Sub4 

On Sub2.Date = Sub4.Date

Answer 1

Use the following:

SELECT *
FROM 
    (
        SELECT *
        FROM 
            (
                SELECT
                  x.id,
                  x.date,
                  x.usohist,
                  x.ovxhist,
                  ABS(CAST((((x.usohist / NULLIF((y.usohist), 0)) - 1) * 100) AS decimal(10, 2))) AS '%USOH',
                  ABS(CAST((((x.ovxhist / NULLIF((y.ovxhist), 0)) - 1) * 100) AS decimal(10, 2))) AS '%OVXH'
                FROM 
                    (
                        SELECT
                          a.date AS adate,
                          MAX(b.date) AS aprevdate
                        FROM uso_ovx_hist a
                        INNER JOIN uso_ovx_hist b
                          ON a.date > b.date
                        GROUP BY a.date
                    ) Sub1
                INNER JOIN uso_ovx_hist x
                    ON sub1.adate = x.date
                INNER JOIN uso_ovx_hist y
                    ON sub1.aprevdate = y.date
            ) Sub2
        INNER JOIN 
            (SELECT * FROM 
                (
                    SELECT
                      z.id,
                      z.id2,
                      z.date,
                      z.uso AS usop,
                      z.ovx AS ovxp,
                      CAST(((z.uso / NULLIF((q.uso), 0) - 1) * 100) AS decimal(10, 2)) AS '%USOP',
                      CAST(((z.ovx / NULLIF((q.ovx), 0) - 1) * 100) AS decimal(10, 2)) AS '%OVXP'
                    FROM 
                        (SELECT
                          c.date AS cdate,
                          MAX(d.date) AS cprevdate
                        FROM uso_ovx_price c
                        INNER JOIN uso_ovx_price d
                          ON c.date > d.date
                        GROUP BY c.date) Sub3
                        INNER JOIN uso_ovx_price z
                          ON sub3.cdate = z.date
                        INNER JOIN uso_ovx_price q
                          ON sub3.cprevdate = q.date
                ) Sub4
            ) t
        ON sub2.date = t.date
    ) t2

You can use online Instant SQL Formatter to format your SQL.

---

Thanks this worked great. I'm having a hard time understanding the point about adding the alias in order for the join to work. Do you mind explaining it to me? I am still kind of new to SQL. Also, how exactly do I make more subqueries or join another table on that query?

Just another: InnerJoin NYSEData on Sub4.Date = NYSEData.Date

I get an error stating that Sub4 object cannot be bound?

I understand that I will need to add another select and from standard in the query in order to add another table or subquery? Please help, thank you.