how to join these two queries together

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to join these two queries together

Author

Topic

bhlgroup
Starting Member

2 Posts

Posted - 2011-07-31 : 09:33:47

I have built a query that calls another query. how can I combine these into one query?

1st query (called qrypounits)

SELECT StockItem.Code, StockItem.name, Unit.Name AS [Purchase Unit], StockItemUnit.MultipleOfBaseUnit AS PCs
FROM (StockItemUnit INNER JOIN (StockItem INNER JOIN StockItemX ON StockItem.ItemID = StockItemX.StockItemXID) ON (StockItemUnit.UnitID = StockItemX.PurchasingUMAID) AND (StockItemUnit.ItemID = StockItem.ItemID)) INNER JOIN Unit ON StockItemUnit.UnitID = Unit.UnitID;

this is the 2nd query which is calling the first and another two tables and then doing a left join

SELECT POPOrderReturn.DocumentNo, POPOrderReturnLine.ItemCode
FROM (POPOrderReturn INNER JOIN POPOrderReturnLine ON POPOrderReturn.POPOrderReturnID = POPOrderReturnLine.POPOrderReturnID) LEFT JOIN qrypounits ON POPOrderReturnLine.ItemCode = qrypounits.Code
WHERE (((POPOrderReturn.DocumentNo)="1472") AND ((qrypounits.Code) Is Null))

Thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-31 : 10:58:04

You can insert it as a subquery, or use it as a CTE (Common table expression). To use it as a CTE, it would be like this:

;WITH qrypounits AS (
SELECT StockItem.Code,
       StockItem.name,
       Unit.Name AS [Purchase Unit],
       StockItemUnit.MultipleOfBaseUnit AS PCs
FROM   (
           StockItemUnit INNER JOIN (
               StockItem INNER JOIN StockItemX ON StockItem.ItemID = StockItemX.StockItemXID
           ) ON (StockItemUnit.UnitID = StockItemX.PurchasingUMAID)
           AND (StockItemUnit.ItemID = StockItem.ItemID)
       )
       INNER JOIN Unit
            ON  StockItemUnit.UnitID = Unit.UnitID

)
SELECT POPOrderReturn.DocumentNo,
       POPOrderReturnLine.ItemCode
FROM   (
           POPOrderReturn INNER JOIN POPOrderReturnLine ON POPOrderReturn.POPOrderReturnID 
           = POPOrderReturnLine.POPOrderReturnID
       )
       LEFT JOIN qrypounits
            ON  POPOrderReturnLine.ItemCode = qrypounits.Code
WHERE  (
           ((POPOrderReturn.DocumentNo) = '1472')
           AND ((qrypounits.Code) IS NULL)
       )

As a subquery it would be:

SELECT POPOrderReturn.DocumentNo,
       POPOrderReturnLine.ItemCode
FROM   (
           POPOrderReturn INNER JOIN POPOrderReturnLine ON POPOrderReturn.POPOrderReturnID 
           = POPOrderReturnLine.POPOrderReturnID
       )
       LEFT JOIN 
       (
			SELECT StockItem.Code,
				   StockItem.name,
				   Unit.Name AS [Purchase Unit],
				   StockItemUnit.MultipleOfBaseUnit AS PCs
			FROM   (
					   StockItemUnit INNER JOIN (
						   StockItem INNER JOIN StockItemX ON StockItem.ItemID = StockItemX.StockItemXID
					   ) ON (StockItemUnit.UnitID = StockItemX.PurchasingUMAID)
					   AND (StockItemUnit.ItemID = StockItem.ItemID)
				   )
				   INNER JOIN Unit
						ON  StockItemUnit.UnitID = Unit.UnitID
       )
       AS qrypounits
            ON  POPOrderReturnLine.ItemCode = qrypounits.Code
WHERE  (
           ((POPOrderReturn.DocumentNo) = '1472')
           AND ((qrypounits.Code) IS NULL)
       )

bhlgroup
Starting Member

2 Posts

Posted - 2011-07-31 : 11:06:16

Thank you very much sunitabeck. you are a star :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources