Consolidating Queries

Question

I'm trying to understand how to combine queries when one of them returns more than one record.

This is an invoicing report where I want to pull in the Serial Numbers of products invoiced. I'll abbreviate the script as much as possible to clarify. Here is my script before adding the serials:

   SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
,      ARM.fcinvoice AS [Invoice No]
,      ARM.fdgldate AS [Post Date]
,      ARI.fitem AS [Item No]
,      ARI.fprodcl AS [Prod Class]
,      ARI.fshipkey AS [Qty Invoiced]
,      ARI.fpartno AS [Part No]
,      ARI.frev AS [Part Rev]
,      ARI.FTOTPRICE AS [Net Invoiced]
,      ARM.fsono AS [Sales No]
,      SOM.fcusrchr2
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono

This returns invoiced line items, their prices, and such. When I pull in the following:

SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
,      ARM.fcinvoice AS [Invoice No]
,      ARM.fdgldate AS [Post Date]
,      ARI.fitem AS [Item No]
,      ARI.fprodcl AS [Prod Class]
,      ARI.fshipkey AS [Qty Invoiced]
,      ARI.fpartno AS [Part No]
,      ARI.frev AS [Part Rev]
,      ARI.FTOTPRICE AS [Net Invoiced]
,      ARM.fsono AS [Sales No]
,      SOM.fcusrchr2
,      LOTC.fcuseinlot 
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono

--** New stuff below: ******
LEFT OUTER JOIN ShItem SHI ON SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT OUTER JOIN ShSrce ON ShSrce.fcShipNo = SHI.fShipNo
                      AND ShSrce.fcItemNo = SHI.fItemNo
LEFT OUTER JOIN QaLotC LOTC ON LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo

The problem is that there can be multiple SHSRCE records per invoice. What is the best way to handle this? Perhaps use a subquery to concatenate the LOTC.fcuseinlot field in order to return one corresponding value per record.

To clarify, the additional query returns more than 1 record per line item invoice because multiple serial number parts can be invoiced on one line item. Ideally, I'd like them to be concatenated like (NCC1701, R2D2, C3PO) etc. That's why I thought about using a subquery to concatenate them.

Answer 1

Combine queries? As in do a UNION?

Answer 2

Still waiting for the OP to clarify the question, but if the multiple SHSRCE records still only relate to one serialnumber (which i am presuming is in LOTC.fcuseinlot)

SELECT
    ARM.fcustno AS [Cust No] ,
    ARM.fbcompany AS [Cust Name] ,
    ARM.fcinvoice AS [Invoice No] ,
    ARM.fdgldate AS [Post Date] ,
    ARI.fitem AS [Item No] ,
    ARI.fprodcl AS [Prod Class] ,
    ARI.fshipkey AS [Qty Invoiced] ,
    ARI.fpartno AS [Part No] ,
    ARI.frev AS [Part Rev] ,
    ARI.FTOTPRICE AS [Net Invoiced] ,
    ARM.fsono AS [Sales No] ,
    SOM.fcusrchr2,
       MAX(LOTC.fcuseinlot)
FROM
    dbo.ARMAST ARM 
JOIN dbo.aritem ARI ON  ARI.FCINVOICE = ARM.FCINVOICE 
JOIN slcdpm SLC ON  SLC.fcustno = ARM.fcustno
LEFT JOIN slcdpm_ext SLCE ON  SLCE.fkey_id = SLC.identity_column
LEFT JOIN somast SOM ON  SOM.fsono = ARM.fsono
LEFT JOIN ShItem SHI ON  SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT JOIN ShSrce ON  ShSrce.fcShipNo = SHI.fShipNo AND ShSrce.fcItemNo = SHI.fItemNo
LEFT JOIN QaLotC LOTC ON  LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo
GROUP BY
    ARM.fcustno ,
    ARM.fbcompany ,
    ARM.fcinvoice ,
    ARM.fdgldate ,
    ARI.fitem ,
    ARI.fprodcl ,
    ARI.fshipkey ,
    ARI.fpartno ,
    ARI.frev ,
    ARI.FTOTPRICE ,
    ARM.fsono ,
    SOM.fcusrchr2

---

can you post some sample data.

If I'm getting you right, take a look at this kind of sql

create table tableA (id int, ref varchar(50))

insert into tableA
select 1, 3536757616
union select 1, 3536757617
union select 1, 3536757618
union select 2, 3536757628
union select 2, 3536757629
union select 2, 3536757630

I know I can simply concatenate the refs by using

SELECT distinct
    id,
    stuff ( ( SELECT
                  '/ ' + ref 
              FROM
                  tableA tableA_1
              where tableA_1.id = tableA_2.id
    FOR XML PATH ( '' ) ) , 1 , 2 , '' )
from TableA tableA_2

to give

1   3536757616/ 3536757617/ 3536757618
2   3536757628/ 3536757629/ 3536757630

Answer 3

What we really need to know is what basis are you going to use to choose which of the possible records has the values you want returned? That will determine the best possibilites for you.

And as you aren't adding any columns and you are using left joins (so no records will be excluded from the joins) what is it you expect to accomplish by adding these joins except to lengthen processing time?

Answer 4

You've got a SQL Server 2000 tag on your question, so I'm not sure this is going to be possible for you. In SQL Server 2005 and above, you can create custom aggregate functions. I use an aggregate function FormDelimitedString, which does exactly what you are looking for.

There is a page here which describes how to implement this function - it gets written as a .NET assembly and then executed from within your queries. With this in place, your query becomes...

SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
-- (a few fields skipped for brevity)
,      SOM.fcusrchr2
-- Aggregate function will combine multiple values into a single string - 'value1, value2, value3' etc
,   dbo.FormDelimitedString(LOTC.fcuseinlot) AS ConcatenatedValues
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono
LEFT OUTER JOIN ShItem SHI ON SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT OUTER JOIN ShSrce ON ShSrce.fcShipNo = SHI.fShipNo
                      AND ShSrce.fcItemNo = SHI.fItemNo
LEFT OUTER JOIN QaLotC LOTC ON LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo

-- Group by clause needed as its an aggregate function
GROUP BY ARM.fcustno, ARM.fbcompany, SOM.fcusrchr2