SELECT AM.fcinvoice AS InvoiceNumber,
       AM.finvdate AS InvoiceDate,
       left(AI.fsokey, 6) AS SalesNumber,
       AM.fpono AS ContractNumber,
       AI.FPRODCL AS ProductType,
       AI.fpartno AS SKUNumber,
       AI.frev AS ProductDescription,
       SL.fcustno AS CustomerID,
       AM.fccompany AS CustomerName,
       SL.fcusrchr1 AS CustomerClass,
       (SELECT SM.fcstate
          FROM    dbo.shmast SM
               INNER JOIN
                  AI
               ON left(AI.fshipkey, 6) = SM.fshipno)
          AS DeliveryState
  FROM       dbo.slcdpm SL
          INNER JOIN
             dbo.armast AM
          ON AM.fcustno = SL.fcustno
       INNER JOIN
          dbo.aritem AI
       ON AM.fcinvoice = AI.fcinvoice
 WHERE SL.ftype = 'C' AND year(AM.finvdate) > 2007

SELECT AM.fcinvoice AS InvoiceNumber,
       AM.finvdate AS InvoiceDate,
       left(AI.fsokey, 6) AS SalesNumber,
       AM.fpono AS ContractNumber,
       AI.FPRODCL AS ProductType,
       AI.fpartno AS SKUNumber,
       AI.frev AS ProductDescription,
       SL.fcustno AS CustomerID,
       AM.fccompany AS CustomerName,
       SL.fcusrchr1 AS CustomerClass,
       (SELECT SM.fcstate
          FROM    dbo.shmast SM
               INNER JOIN  dbo.aritem 
                  AI1
               ON left(AI1.fshipkey, 6) = SM.fshipno)
          AS DeliveryState
  FROM       dbo.slcdpm SL
          INNER JOIN
             dbo.armast AM
          ON AM.fcustno = SL.fcustno
       INNER JOIN
          dbo.aritem AI
       ON AM.fcinvoice = AI.fcinvoice
 WHERE SL.ftype = 'C' AND year(AM.finvdate) > 2007

quote:

---

Originally posted by DavidChel

Let me explain.

I have to create this huge "data sheet" with all this information about invoices. Some of these fields will have to be pulled from other modules in our database and not all of them will have answers.

So, in this case I was querying our shipping tables SM as a subquery from within the invoicing main query and AI is the alias for aritem. For each record pulled from invoicing I want the corresponding shipping record pulled. The join is left(AI.fshipkey, 6) = SM.fshipno) because on those invoices generated from sales orders the first 6 characters in the AI (Aritem invoicing table) corresponds to the shipper number.

Make sense?

---

SELECT AM.fcinvoice AS InvoiceNumber,
       AM.finvdate AS InvoiceDate,
       left(AI.fsokey, 6) AS SalesNumber,
       AM.fpono AS ContractNumber,
       AI.FPRODCL AS ProductType,
       AI.fpartno AS SKUNumber,
       AI.frev AS ProductDescription,
       SL.fcustno AS CustomerID,
       AM.fccompany AS CustomerName,
       SL.fcusrchr1 AS CustomerClass,
       SM.fcstate   AS DeliveryState
  FROM       dbo.slcdpm SL
          INNER JOIN
             dbo.armast AM
          ON AM.fcustno = SL.fcustno
       INNER JOIN
          dbo.aritem AI
       ON AM.fcinvoice = AI.fcinvoice
INNER JOIN dbo.shmast SM
 ON left(AI.fshipkey, 6) = SM.fshipno
 WHERE SL.ftype = 'C' AND year(AM.finvdate) > 2007

quote:

---

Originally posted by DavidChel

Because this one query is going to attach to tons of different areas. I will literally have around 9 different joins and many of them will be left outer. I know from experience that if I try to do this without nested subqueries I will miss records.

This is why I am using nested sub queries.

Is there something wrong with the way I was trying to do it?

---

quote:

---

Originally posted by DavidChel

So, how do I relate it to the main query? I can't do so by using the Alias from the main query?

---