Join two different SQL Queries to make one view

If I understand you right, you have a view, and now you want to mix the AVG values into the first view?

I can not really imagine, how the results look like, therefor an more generic answer.
You can create a view based on your fist view and add the calculated fields there (here AVG).

You can also create two independent views and mix them together in a third view, as long you have a unique ID in both source views so the view knows, what belongs together.

As the AVG view is a grouped view, you may have to construct the ID (i.e. by min, max,first, last aggregate function) as long as all records which are grouped together have the same ID.
The ID can be any field, what is common to the grouped AVG result, it just has to exist in both views.

You should be able to join via the stock number, just treated the average calculation as a derived table:

SELECT
        X.StockCode AS topLevelPart
      , avs.AVGprice
      , A.Component AS L1
      , B.Component AS L2
      , C.Component AS L3
      , D.Component AS L4
      , E.Component AS L5
      , F.Component AS L6
      , G.Component AS L7
      , H.Component AS L8
      , COALESCE(H.Component, G.Component, F.Component, E.Component, D.Component, C.Component, B.Component, A.Component) AS sort_by
FROM (
        SELECT
                StockCode
              , PartCategory
        FROM dbo.InvMaster
        GROUP BY StockCode
               , PartCategory
        ) AS X
LEFT JOIN (
        SELECT
                  InvMaster.StockCode
                , AVG(PorMasterDetail.MPrice) AS AVGPrice
        FROM PorMasterDetail
        INNER JOIN PorMasterHdr ON PorMasterDetail.PurchaseOrder = PorMasterHdr.PurchaseOrder
        INNER JOIN InvMaster ON PorMasterDetail.MStockCode = InvMaster.StockCode
        WHERE (PorMasterHdr.OrderDueDate > DATEADD(m, -6, GETDATE()))
                AND (PorMasterHdr.CancelledFlag <> 'Y')
        GROUP BY InvMaster.StockCode
        HAVING (SUM(PorMasterDetail.MOrderQty) > 0)
        ) avs ON X.StockCode = avs.StockCode
INNER JOIN dbo.BomStructure AS A ON A.ParentPart = X.StockCode
LEFT OUTER JOIN dbo.BomStructure AS B ON B.ParentPart = A.Component
LEFT OUTER JOIN dbo.BomStructure AS C ON C.ParentPart = B.Component
LEFT OUTER JOIN dbo.BomStructure AS D ON D.ParentPart = C.Component
LEFT OUTER JOIN dbo.BomStructure AS E ON E.ParentPart = D.Component
LEFT OUTER JOIN dbo.BomStructure AS F ON F.ParentPart = E.Component
LEFT OUTER JOIN dbo.BomStructure AS G ON G.ParentPart = F.Component
LEFT OUTER JOIN dbo.BomStructure AS H ON H.ParentPart = G.Component
WHERE (B.StructureOffDate IS NULL)
        AND (C.StructureOffDate IS NULL)
        AND (D.StructureOffDate IS NULL)
        AND (E.StructureOffDate IS NULL)
        AND (A.StructureOffDate IS NULL)
        AND (F.StructureOffDate IS NULL)
        AND (G.StructureOffDate IS NULL)
        AND (H.StructureOffDate IS NULL)
        OR (B.StructureOffDate > GETDATE())
        AND (C.StructureOffDate > GETDATE())
        AND (D.StructureOffDate > GETDATE())
        AND (E.StructureOffDate > GETDATE())
        AND (A.StructureOffDate > GETDATE())
        AND (F.StructureOffDate > GETDATE())
        AND (G.StructureOffDate > GETDATE())
        AND (H.StructureOffDate > GETDATE())
;

Select allOpen in new window

Opinion:
Views should NOT contain an ORDER BY clause.

In many (many!) cases views are used in conjunction with a where clause or are joined to other tables. As soon as either of these things occur you almost certainly have wasted time and effort (slowing down your result unnecessarily).

Apply an ORDER BY clause at the very end of the total query, not hidden inside a view,

@PortletPaul
I have tried your solution but its only showing the AVGPrice for the TopLevelPart, I need the prices for each component
I should have explained my question better, sorry.

I need to match the AVGPrice against each level in the bom
I was hoping to get the following table
topLevelPart,  AVGPrice ,L1, L1AVG,L2,L2AVG etc

It might not be possible.

Thanks

This was only asked in the SQL Topic Area.  This is the generic Structured Query Language area.

As SQL can vary greatly across database products and versions, please provide this information.

I see 'dbo' and I think SQL Server but I hate to guess.

dbo.BomStructure AS A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
dbo.BomStructure AS B ON B.ParentPart = A.Component LEFT OUTER JOIN
                       

I see things like this and cringe.  You can probably get what you need without all the joins back to the same table.

As far as the question asked about AVG:
Please provide some raw sample data and expected results.  Then we an set up a test case and provide working SQL.

The closer your sample data is to your actual problem, the easier it will be to take our SQL and apply it.

Yes its SQL Server.
The tables are: BOMStructure
 ParentPart, Route, Component, StructureOnDate, StructureOffDate, QtyPer
1234,    0,    xyz,   01/01/2010,   null,   1
1234,    0,    xyz1,   01/01/2012,   null,   1
1234,    0,    xyz2,   01/01/2010,  01/01/2012,   1
1234,    0,    xyz3,   01/01/2010,   null,   10
xyz ,    0,    abc,   01/01/2010,   null,   1
xyz ,    0,    abc1,   01/01/2010,   null,   1

As the data above shows part 1234 is made up of several components and some of the components are made up of different components
The first code posted shows the results as
toplevel,  L1,  L2,  L3 upto L6
1234      xyz  abc  null
1234      xyz1 null null
1234      xyz2 null null
xyz        abc      null  null
xyz        abc1  null  null


This code below returns the average cost of each stockcode of the last six months which is held across two tables, PoMasterHeader and PorMasterDetail

SELECT        AVG(PorMasterDetail.MPrice) AS AVGPrice, PorMasterDetail.MStockCode
FROM            PorMasterDetail INNER JOIN
                         PorMasterHdr ON PorMasterDetail.PurchaseOrder = PorMasterHdr.PurchaseOrder
WHERE        (PorMasterHdr.OrderDueDate > DATEADD(m, - 6, GETDATE())) AND (PorMasterHdr.CancelledFlag <> 'Y')
GROUP BY PorMasterDetail.MStockCode
HAVING        (SUM(PorMasterDetail.MOrderQty) > 0)

Select allOpen in new window

Using data above would show
AVGPrice  MStockCode
123.89      1234
0.98          xyz
1.98          xyz1
6.98          xyz2
4.56          xyz3
0.25        abc
0.35        abc1

Ideally I would like to get the results as
toplevel, AVGPrice,  L1,AVGPrice1,   L2, AVGPrice2,   L3 , AVGPrice3, up to L6
1234          123.89    xyz       0.98         abc     0.25
1234          123.89    xyz1     1.98        
1234          123.89    xyz2      6.98  
xyz             0.98       abc        0.25
xyz             0.98       abc1      0.35  

No sure if this helps any

Thanks

        

Version of SQL Server?  This is important because I have a feeling that some of the newer SQL features will make the solution simpler.


What I would like to do is set up a complete test case that takes the data you provide, load it into tables and provide SQL that produces the expected results you provide.


In the last comment, you five me a table BOMStructure and some data.  Then give me a query that references PoMasterHeader and PorMasterDetail tables.

It is kind of hard to create a fiddle based on what you have provided.

I can "guess" but prefer not to because I normally guess wrong and it can confuse everyone involved and take a lot longer to come up with an acceptable solution.

All that said,
Seems like you have a hierarchical data model and you want a pivoted result?

I'm not familiar with all the SQL Server tricks so not sure the best way to do hierarchal queries then pivot the results.

It's a bill of materials (bom) explosion.  Typically boms use recursion on SQL 2005 or later.