Join two queries

debugcn Published at Dev

JeffreyLazo

I am trying to join these two queries on:

InventoryItemID and can't seem to make it work. I have tried a number of solutions, but can't wrap my head around it. I am using Microsoft SQL.

select t1.itemcode, t1.Description, t1.MinimumQuantity, t1.QuantityInStore, t2.qty as OnOrder, t1.InventoryItemID
from
    (
select i.InventoryItemID,  i.ItemCode, i.Description, MinimumQuantity, ii.QuantityInStore
    from InventoryInfo i
    inner join InventoryStoreInfo ii
        on i.InventoryItemID = ii.inventoryitemid
    inner join PurchaseOrderItemInfo p
        on ii.InventoryItemID = p.InventoryItemID
    inner join PurchaseOrderInfo po
        on p.PurchaseOrderID = po.PurchaseOrderID
    inner join VendorInfo v
        on po.VendorID = v.VendorID
    where InventoryCategoryID = '1'
    and i.IsActive = '1'
    ) as t1
left join 
    (
select InventoryItemID, sum(Quantity) as QTY, Description
    from PurchaseOrderItemInfo p
    inner join PurchaseOrderInfo po
        on p.PurchaseOrderID = po.PurchaseOrderID
    where p.status = '0'
    group by InventoryItemID, Description
    ) as t2
on t1.inventoryitemid = t2.inventoryitemid

and

select pi.InventoryItemID, v.VendorName, max(p.PODateTime) as latest
from PurchaseOrderItemInfo pi
inner join PurchaseOrderInfo p
    on pi.PurchaseOrderID = p.PurchaseOrderID
inner join InventoryInfo i
    on pi.InventoryItemID = i.InventoryItemID
inner join VendorInfo v
    on p.VendorID = v.VendorID
group by pi.inventoryitemid, p.PODateTime,i.description, v.VendorName
order by latest desc

Juan Carlos Oropeza

You wrap each query between parenthesis to create subquery (queryA) and (queryB)

Then assing an alias to each subquery and perfom the join

SELECT aliasA.*, aliasB.*
FROM
  (queryA) aliasA
INNER JOIN  (queryB)  aliasB
  ON aliasA.ID =  aliasB.ID

USING YOUR QUERY

SELECT FIRSTQUERY.*, SECONDQUERY.*
FROM 
    (
    select t1.itemcode, t1.Description, t1.MinimumQuantity, t1.QuantityInStore, t2.qty as OnOrder, t1.InventoryItemID
    from
        (
        select i.InventoryItemID,  i.ItemCode, i.Description, MinimumQuantity, ii.QuantityInStore
        from InventoryInfo i
        inner join InventoryStoreInfo ii
            on i.InventoryItemID = ii.inventoryitemid
        inner join PurchaseOrderItemInfo p
            on ii.InventoryItemID = p.InventoryItemID
        inner join PurchaseOrderInfo po
            on p.PurchaseOrderID = po.PurchaseOrderID
        inner join VendorInfo v
            on po.VendorID = v.VendorID
        where InventoryCategoryID = '1'
        and i.IsActive = '1'
        ) as t1
    left join 
        (
        select InventoryItemID, sum(Quantity) as QTY, Description
        from PurchaseOrderItemInfo p
        inner join PurchaseOrderInfo po
            on p.PurchaseOrderID = po.PurchaseOrderID
        where p.status = '0'
        group by InventoryItemID, Description
        ) as t2
    on t1.inventoryitemid = t2.inventoryitemid
    ) FIRSTQUERY
    INNER JOIN
    (
    select pi.InventoryItemID, v.VendorName, max(p.PODateTime) as latest
    from PurchaseOrderItemInfo pi
    inner join PurchaseOrderInfo p
        on pi.PurchaseOrderID = p.PurchaseOrderID
    inner join InventoryInfo i
        on pi.InventoryItemID = i.InventoryItemID
    inner join VendorInfo v
        on p.VendorID = v.VendorID
    group by pi.inventoryitemid, p.PODateTime,i.description, v.VendorName
    order by latest desc
    ) SECONDQUERY
    ON FIRSTQUERY.InventoryItemID  = SECONDQUERY.InventoryItemID

Collected from the Internet

Please contact [email protected] to delete if infringement.