NewtoCodedUI
NewtoCodedUI

Reputation: 33

how to join two queries in sql?

I want to join these two queries.

I want the columns FulliteName, text, BeginningBalance, QuantityReceived,DOS,Loss, RequestedQuantity .

I tried the following query. But only the first part is executed.

SELECT CI.ItemID,
    FullItemName,
    iub.UnitOfIssueID,
    us.TEXT
FROM Commodity.ItemRRFGroup CI
INNER JOIN dbo.vwGetAllItems y
    ON y.ID = ItemID
INNER JOIN ItemUnitBase iub
    ON y.ID = iub.ID
INNER JOIN UnitOfIssue us
    ON iub.UnitOfIssueID = us.ID
LEFT JOIN (
    SELECT RD.RequestID,
        y.RRGroupID,
        y.PeriodID,
        ItemID,
        CONCAT (
            Convert(DATE, StartDate),
            ' to ',
            Convert(DATE, EndDate)
            ) Period,
        BeginningBalance,
        QuantityReceived,
        DOS,
        Loss,
        RequestedQuantity
    FROM RRF.RequestDetail RD
    INNER JOIN RRF.Request y
        ON y.RequestID = RD.RequestID
    INNER JOIN RRF.Period x
        ON x.PeriodID = y.PeriodID
    INNER JOIN OrderDetail o
        ON o.OrderID = y.orderID
    ) AS x
    ON CI.ItemID = x.ItemID

Upvotes: 0

Views: 68

Answers (1)

Arion
Arion

Reputation: 31239

Maybe you change this:

Select 
   CI.ItemID, 
   FullItemName,
   iub.UnitOfIssueID,
   us.text
   .....

To this:

Select 
   CI.ItemID, 
   FullItemName,
   iub.UnitOfIssueID ,
   us.text,
   x.BeginningBalance,
   x.QuantityReceived,
   x.DOS,
   x.Loss, 
   x.RequestedQuantity
   ....

Update

If you do not use the alias x of the sub query. Then you are not going to get the columns from x.

Upvotes: 3

Related Questions