Avoiding Multiple records

dockraz · #1 (**permalink**) 02-16-11, 18:53

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

Internet Services

Web Development

Digital Marketing

Consumer Tech