asked on

SQL - Subquery Syntax

I have a query that returns a number of rows with a given set of criteria. I require to subquery one of the tables with a different criteria value to return 1 extra column. I am having trouble getting the syntax correct.

The original query is:

SELECT        TOP (100) PERCENT Floyd.dbo.SLCustomerAccount.CustomerAccountNumber AS [Acct Number], 
                         Floyd.dbo.SLCustomerAccount.CustomerAccountName AS [Acct Name], Floyd.dbo.TransactionHistory.TransactionDate AS [Invoice Date], 
                         Floyd.dbo.TransactionHistory.SecondReference AS [Invoice Number], Floyd.dbo.StockItem.Code AS [Item Number], Floyd.dbo.StockItem.Name, 
                         Floyd.dbo.StockItem.Description, Floyd.dbo.TransactionHistory.Quantity, Floyd.dbo.TransactionHistory.Reference AS [Floyd Order], 
                         Floyd.dbo.TransactionHistory.UnitIssuePrice AS [Item Price], Floyd.dbo.TransactionHistory.UnitDiscountValue AS Discount, 
                         Floyd.dbo.TransactionHistory.IssueValue AS [Line Total]
FROM            Floyd.dbo.StockItem INNER JOIN
                         Floyd.dbo.TransactionHistory ON Floyd.dbo.StockItem.ItemID = Floyd.dbo.TransactionHistory.ItemID INNER JOIN
                         Floyd.dbo.SLCustomerAccount ON Floyd.dbo.TransactionHistory.SourceAreaReference = Floyd.dbo.SLCustomerAccount.CustomerAccountNumber
WHERE        (Floyd.dbo.TransactionHistory.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) AND 
                         (Floyd.dbo.TransactionHistory.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) AND 
                         (Floyd.dbo.SLCustomerAccount.CustomerAccountNumber = '118') AND (Floyd.dbo.TransactionHistory.TransactionTypeID = 15)
ORDER BY [Invoice Date]

Open in new window

I need to return [Order Number] from:
subqueryFloyd.dbo.TransactionHistory.SecondReference AS [Order Number]
WHERE subqueryFloyd.dbo.TransactionHistory.TransactionTypeID = 13
AND subqueryFloyd.dbo.TransactionHistory.Reference = mainqueryFloyd.dbo.TransactionHistory.Reference

Lee Wadwell

You can join the same table in more than once ... just use a different alias for each occurance
e.g.

SELECT  Floyd.dbo.SLCustomerAccount.CustomerAccountNumber AS [Acct Number], 
        Floyd.dbo.SLCustomerAccount.CustomerAccountName AS [Acct Name], Floyd.dbo.TransactionHistory.TransactionDate AS [Invoice Date], 
        Floyd.dbo.TransactionHistory.SecondReference AS [Invoice Number], Floyd.dbo.StockItem.Code AS [Item Number], Floyd.dbo.StockItem.Name, 
        Floyd.dbo.StockItem.Description, Floyd.dbo.TransactionHistory.Quantity, Floyd.dbo.TransactionHistory.Reference AS [Floyd Order], 
        Floyd.dbo.TransactionHistory.UnitIssuePrice AS [Item Price], Floyd.dbo.TransactionHistory.UnitDiscountValue AS Discount, 
        Floyd.dbo.TransactionHistory.IssueValue AS [Line Total]
        SecondReference.SecondReference AS [Order Number]
FROM    Floyd.dbo.StockItem 
INNER JOIN Floyd.dbo.TransactionHistory ON Floyd.dbo.StockItem.ItemID = Floyd.dbo.TransactionHistory.ItemID 
INNER JOIN Floyd.dbo.SLCustomerAccount ON Floyd.dbo.TransactionHistory.SourceAreaReference = Floyd.dbo.SLCustomerAccount.CustomerAccountNumber
INNER JOIN Floyd.dbo.TransactionHistory SecondReference ON SecondReference.TransactionTypeID = 13 AND SecondReference.Reference = Floyd.dbo.TransactionHistory.Reference
WHERE   (Floyd.dbo.TransactionHistory.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) 
AND     (Floyd.dbo.TransactionHistory.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) 
AND     (Floyd.dbo.SLCustomerAccount.CustomerAccountNumber = '118') AND (Floyd.dbo.TransactionHistory.TransactionTypeID = 15)
ORDER BY [Invoice Date]

Open in new window

TeDeSm

ASKER

Thanks lwadwell. I get duplicates trying your method. My main query outputs 47 rows without any changes, 157 if I add in TransactionHistory again filtered on TransactionTypeID=13.

LinInDenver

As long as your subquery returns a single row per matched join, try adding it as another "field" you are selecting. You should be able to join from your query into a sub select like this without any issues.

SELECT TOP (100) PERCENT Floyd.dbo.SLCustomerAccount.CustomerAccountNumber AS [Acct Number],
Floyd.dbo.SLCustomerAccount.CustomerAccountName AS [Acct Name],
      Floyd.dbo.TransactionHistory.TransactionDate AS [Invoice Date],
Floyd.dbo.TransactionHistory.SecondReference AS [Invoice Number],
      Floyd.dbo.StockItem.Code AS [Item Number], Floyd.dbo.StockItem.Name,
Floyd.dbo.StockItem.Description, Floyd.dbo.TransactionHistory.Quantity,
      Floyd.dbo.TransactionHistory.Reference AS [Floyd Order],
Floyd.dbo.TransactionHistory.UnitIssuePrice AS [Item Price],
       Floyd.dbo.TransactionHistory.UnitDiscountValue AS Discount,
Floyd.dbo.TransactionHistory.IssueValue AS [Line Total],

      ( select sub.SecondReference
      FROM Floyd.dbo.TransactionHistory sub
      WHERE sub.TransactionTypeID = 13
      AND sub.Reference = Floyd.dbo.TransactionHistory.Reference) as Order_Number

FROM Floyd.dbo.StockItem INNER JOIN
Floyd.dbo.TransactionHistory ON Floyd.dbo.StockItem.ItemID = Floyd.dbo.TransactionHistory.ItemID INNER JOIN
Floyd.dbo.SLCustomerAccount ON Floyd.dbo.TransactionHistory.SourceAreaReference = Floyd.dbo.SLCustomerAccount.CustomerAccountNumber
WHERE (Floyd.dbo.TransactionHistory.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) AND
(Floyd.dbo.TransactionHistory.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) AND
(Floyd.dbo.SLCustomerAccount.CustomerAccountNumber = '118') AND (Floyd.dbo.TransactionHistory.TransactionTypeID = 15)
ORDER BY [Invoice Date]

Lee Wadwell

Do you need to apply the date range check again too? i.e.

SELECT  Floyd.dbo.SLCustomerAccount.CustomerAccountNumber AS [Acct Number], 
        Floyd.dbo.SLCustomerAccount.CustomerAccountName AS [Acct Name], Floyd.dbo.TransactionHistory.TransactionDate AS [Invoice Date], 
        Floyd.dbo.TransactionHistory.SecondReference AS [Invoice Number], Floyd.dbo.StockItem.Code AS [Item Number], Floyd.dbo.StockItem.Name, 
        Floyd.dbo.StockItem.Description, Floyd.dbo.TransactionHistory.Quantity, Floyd.dbo.TransactionHistory.Reference AS [Floyd Order], 
        Floyd.dbo.TransactionHistory.UnitIssuePrice AS [Item Price], Floyd.dbo.TransactionHistory.UnitDiscountValue AS Discount, 
        Floyd.dbo.TransactionHistory.IssueValue AS [Line Total]
        SecondReference.SecondReference AS [Order Number]
FROM    Floyd.dbo.StockItem 
INNER JOIN Floyd.dbo.TransactionHistory ON Floyd.dbo.StockItem.ItemID = Floyd.dbo.TransactionHistory.ItemID 
INNER JOIN Floyd.dbo.SLCustomerAccount ON Floyd.dbo.TransactionHistory.SourceAreaReference = Floyd.dbo.SLCustomerAccount.CustomerAccountNumber
INNER JOIN Floyd.dbo.TransactionHistory SecondReference ON SecondReference.TransactionTypeID = 13 AND SecondReference.Reference = Floyd.dbo.TransactionHistory.Reference
WHERE   (Floyd.dbo.TransactionHistory.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) 
AND     (Floyd.dbo.TransactionHistory.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) 
AND     (SecondReference.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) 
AND     (SecondReference.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) 
AND     (Floyd.dbo.SLCustomerAccount.CustomerAccountNumber = '118') AND (Floyd.dbo.TransactionHistory.TransactionTypeID = 15)
ORDER BY [Invoice Date]

Open in new window

TeDeSm

ASKER

I think the problem is the subquery could return more than one row. Perhaps a better way would be to run as part of a stored procedure which would have been my next step. Within that sp I think the subquery could be run with DISTINCT to give single rows per Floyd.dbo.TransactionHistory.Reference, and then matched with the main query in the final output.

ASKER CERTIFIED SOLUTION

LinInDenver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TeDeSm

ASKER

All three examples work as the single value returned is unique to each instance of the linked column .Reference. Thanks for the help in getting the syntax correct.

DISTINCT is my preferred method as in the SQL below:

SELECT TOP (100) PERCENT Floyd.dbo.SLCustomerAccount.CustomerAccountNumber AS [Acct Number],
Floyd.dbo.SLCustomerAccount.CustomerAccountName AS [Acct Name], Floyd.dbo.TransactionHistory.TransactionDate AS [Invoice Date],
Floyd.dbo.TransactionHistory.SecondReference AS [Invoice Number],
(SELECT DISTINCT SecondReference
FROM Floyd.dbo.TransactionHistory AS sub
WHERE (TransactionTypeID = 12) AND (Reference = Floyd.dbo.TransactionHistory.Reference)) AS Order_Number, Floyd.dbo.StockItem.Code AS [Item Number],
Floyd.dbo.StockItem.Name, Floyd.dbo.StockItem.Description, Floyd.dbo.TransactionHistory.Quantity, Floyd.dbo.TransactionHistory.Reference AS [Floyd Order],
Floyd.dbo.TransactionHistory.UnitIssuePrice AS [Item Price], Floyd.dbo.TransactionHistory.UnitDiscountValue AS Discount,
Floyd.dbo.TransactionHistory.IssueValue AS [Line Total]
FROM Floyd.dbo.StockItem INNER JOIN
Floyd.dbo.TransactionHistory ON Floyd.dbo.StockItem.ItemID = Floyd.dbo.TransactionHistory.ItemID INNER JOIN
Floyd.dbo.SLCustomerAccount ON Floyd.dbo.TransactionHistory.SourceAreaReference = Floyd.dbo.SLCustomerAccount.CustomerAccountNumber
WHERE (Floyd.dbo.TransactionHistory.TransactionDate >= CONVERT(DATETIME, '2012-06-01 00:00:00', 102)) AND
(Floyd.dbo.TransactionHistory.TransactionDate <= CONVERT(DATETIME, '2012-08-31 00:00:00', 102)) AND
(Floyd.dbo.SLCustomerAccount.CustomerAccountNumber = '118') AND (Floyd.dbo.TransactionHistory.TransactionTypeID = 15)
ORDER BY [Invoice Date]