• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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
0
TeDeSm
Asked:
TeDeSm
  • 3
  • 2
  • 2
1 Solution
 
lwadwellCommented:
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

0
 
TeDeSmAuthor Commented:
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.
0
 
LinInDenverCommented:
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]
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lwadwellCommented:
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

0
 
TeDeSmAuthor Commented:
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.
0
 
LinInDenverCommented:
Yes, multiple rows returned would be a problem with what I suggested. You *could* do distinct, or min or max with my idea, but I don't know if that would give you the right output.

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

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

   ( select MAX (sub.SecondReference)
      FROM Floyd.dbo.TransactionHistory sub
      WHERE sub.TransactionTypeID = 13
      AND sub.Reference = Floyd.dbo.TransactionHistory.Reference) as Order_Number
0
 
TeDeSmAuthor Commented:
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]
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now