Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Unanswered: Avoiding Multiple records

    Hey all -

    I'm getting a query with multiple results ( and there should only be 1 result ) and I'm needing help with rewriting it so it only shows 1 result.

    Here is my current query:
    Code:
    select t1.InvoiceID
         , t1.CustomInvoiceNumber
         , concat( t2.FirstName, ' ', left( t2.LastName, 1 ) ) as Salesman
         , t1.TaxableItems
         , t1.Total - t1.Tax as InvSubtotal
         , q1.ItemCost
         , q2.LaborCost
         , q3.UnitCost
      from invInvoice as t1
    left outer 
      join conAdditionalContacts as t2 
        on t2.UserID = t1.SalesmanID and t2.PrimaryContact = 1
    left outer 
      join invJobs as t3 
        on t3.InvoiceID = t1.InvoiceID
    left outer 
      join invUnitSales as t4 
        on t4.InvoiceID = t1.InvoiceID
    left outer 
      join ( select InvoiceID
                  , sum( ifnull( ExtendedCost, 0 ) ) as ItemCost 
               from invItems group by 1 ) as q1 
        on q1.InvoiceID = t1.InvoiceID
    left outer 
      join ( select JobID
                  , sum( ifnull( Cost, 0 ) ) as LaborCost 
               from invLabor group by 1 ) as q2 
        on q2.JobID = t3.JobID
    left outer 
      join ( select UnitID
                  , sum( ifnull( Invoice, 0 ) ) 
                    + sum( ifnull( FreightCost, 0 ) ) 
                    + sum( ifnull( SetupCost, 0 ) ) 
                    + sum( ifnull( PDICost, 0 ) ) 
                    + sum( ifnull( RepairsCost, 0 ) ) 
                    + sum( ifnull( OtherCost, 0 ) ) 
                    + sum( ifnull( InstallationCost, 0 ) ) 
                    + sum( ifnull( AccessoriesCost, 0 ) ) 
                    + sum( ifnull( LoJackC, 0 ) ) 
                    + sum( ifnull( AlarmC, 0 ) ) 
                    + sum( ifnull( PaintProtC, 0 ) ) 
                    + sum( ifnull( IntProtC, 0 ) ) 
                    + sum( ifnull( UnderCoatC, 0 ) ) 
                    + sum( ifnull( ChemPkgC, 0 ) ) 
                    + sum( ifnull( BatTenderC, 0 ) ) as UnitCost
              from untUnitInfo group by 1 ) as q3 
        on q3.UnitID = t4.UnitID
    where t1.Status = 3
      and t1.Total <> 0
      and t1.CustomInvoiceNumber = 1024
      and t1.SalesmanID > 0
    The result set I get is
    Code:
    +-----------+---------------------+------------+--------------+-------------+----------+-----------+----------+
    | InvoiceID | CustomInvoiceNumber | Salesman   | TaxableItems | InvSubtotal | ItemCost | LaborCost | UnitCost |
    +-----------+---------------------+------------+--------------+-------------+----------+-----------+----------+
    |        24 | 1024                | MICHELLE S |        48.50 |      180.00 |    14.75 |    105.00 |     NULL |
    |        24 | 1024                | MICHELLE S |        48.50 |      180.00 |    14.75 |      NULL |     NULL |
    +-----------+---------------------+------------+--------------+-------------+----------+-----------+----------+
    I think I know the reason for this - there are 2 jobs on this invoice ( invJobs t3 ) - but only 1 of them had labor on them, creating a cost for q2...

    But I have no clue how to write this to get just the first line.

    thanks again
    Last edited by dockraz; 02-16-11 at 19:56. Reason: more to add

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •