SQL query one to many relationship join without duplicates

I’m running queries in SQL Server 2008.. I have a sales table and a payments table.. sometimes a sale has multiple methods of payment (part giftcard + part cash or part credit + part cash etc..) so what I want to do is list the sales and the payments for each sale in a table.

If I do a LEFT JOIN ON sales.SaleID = payments.SaleID I get duplicate sales rows when there are more than one matching payment rows..

  • Fastest way to compare polygon geometry types
  • How to remove foreign key constraint in sql server?
  • SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment
  • What does the SQL # symbol mean and how is it used?
  • How do I make a copy of a table within SQL Server Enterprise Manager?
  • Sending emails automatically using SQL Server job
  • So what I have been doing is getting all the sales and a count of how many matching payment rows there are with (SELECT COUNT(*) FROM payments WHERE payments.SaleID = sales.SaleID) AS NumOfPayments. Then in my PHP script I check the number of payments and if it is > 1 I then run another query to get the payment details.

    The output I am trying to get would look something like this

    -----------------------------------------------------
    | SaleID |  SaleDate  | Amount  | Payments          |
    -----------------------------------------------------
    |    123 | 2013-07-23 | $ 19.99 | Cash:     $ 19.99 |
    |    124 | 2013-07-23 | $  7.53 | Cash:     $  7.53 |
    |    125 | 2013-07-23 | $174.30 | Credit:   $124.30 |
    |        |            |         | GiftCard: $ 50.00 |
    |    126 | 2013-07-23 | $ 79.99 | Cash:     $ 79.99 |
    |    127 | 2013-07-23 | $100.00 | Credit:   $ 90.00 |
    |        |            |         | Cash:     $ 10.00 |
    -----------------------------------------------------
    

    Where sale 125 and 127 have multiple payments listed but the sale information only appears once and is not duplicated for each payment.

    The sales and payments tables look like this:

    Sales                              Payments
    ---------------------------------  --------------------------------------------
    | SaleID |  SaleDate  | Amount  |  | PaymentID | SaleID | PmtMethod |  PmtAmt |
    ---------------------------------  --------------------------------------------
    |    123 | 2013-07-23 | $ 19.99 |  |       158 |    123 |        4  | $ 19.99 |
    |    124 | 2013-07-23 | $  7.53 |  |       159 |    124 |        4  | $  7.53 |
    |    125 | 2013-07-23 | $174.30 |  |       160 |    125 |        2  | $124.30 |
    |    126 | 2013-07-23 | $ 79.99 |  |       161 |    125 |        3  | $ 50.00 |
    |    127 | 2013-07-23 | $100.00 |  |       162 |    126 |        4  | $ 79.99 |
    ---------------------------------  |       163 |    127 |        2  | $ 90.00 |
                                       |       164 |    127 |        4  | $ 10.00 |
                                       --------------------------------------------
    

    I feel like if I can do it with just SQL it will be faster. Is there a way to accomplish this with pure SQL instead of having to use server side code to run conditional queries.

  • Is the NOLOCK (Sql Server hint) bad practice?
  • How to Create Dynamic Columns in SSRS Report
  • Strategy for storing supplemental data alongside imported data
  • trigger in sql problem
  • What to use? View or temporary Table
  • Get all dates between two dates in SQL Server
  • 4 Solutions collect form web for “SQL query one to many relationship join without duplicates”

    I wouldn’t mix data retrieval and data display, which is what I think you are asking about. Do you have some sort of column to indicate which payment should be displayed first? I’m thinking something like:

    SELECT columnlist, 
    rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID)
    FROM sales JOIN payments ON sales.salesID=payments.salesID
    

    Then, in your GUI, just display the values for the first 3 columns where RN = 1, and blank out the values where RN > 1.

    It is probably easier to do this in the interface.

    The basic query you want is:

      select s.saleID, s.SaleDate, s.Amount,
             p.PaymentType, p.PaymentAmount,
             ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum
      from sales s join
           payments p
           on p.saleID = s.saleId
      order by 1, 2
    

    However, you are trying to blank-out fields. To do this, you need to convert all the fields to strings and then check if which are on the first line for each SaleId:

    select (case when seqnum > 1 then '' else CAST(SaleId as varchar(255)) end) as SaleId,
           (case when seqnum > 1 then '' else CONVERT(varchar(10), SaleDate, 121) end) as SaleDate,
           (case when seqnum > 1 then '' else '$'+STR(amount, 6, 2) end) as Amount,
           PaymentType, PaymentAmount
    from (select s.saleID, s.SaleDate, s.Amount,
                 p.PaymentType, p.PaymentAmount,
                 ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum
          from sales s join
               payments p
               on p.saleID = s.saleId
         ) sp
    order by SaleId, SaleDate;
    

    This is not the type of operation that SQL is designed for. SQL works with tables, where all columns have the same meaning. Here, you are introducing a different meaning for the column, depending on its position in the sale. Yes, SQL can do it. No, it is not easy.

    Following query will give the results you need, you need to skip the SalesIDToSort column.

      ;with SalesData
      as
      (
        select 
          Sales.SalesID, Sales.SalesDate, Sales.TotalAmount, Payments.PaymentMode, Payments.Amount,
          Row_Number() over(Partition by Sales.SalesID order by Payments.PaymentID) RowNum
        from 
          Payments
          inner join Sales on Payments.SalesID = Sales.SalesID
      )
      select SalesID, SalesDate, TotalAmount, PaymentMode, Amount, SalesID SalesIDToSort
      from 
        SalesData
      where
        SalesData.RowNum = 1
      union all
      select null, null, null, PaymentMode, Amount, SalesID SalesIDToSort
      from 
        SalesData
      where
        SalesData.RowNum > 1
      order by 6
    

    Working demo

    You could try a gourp by function such as Format to:

    select salesid, salesDate, Amount, FORMAT(payment, 9999.999) as payments
    from FROM payments 
    WHERE payments.SaleID = sales.SaleID
    group by salesid;
    

    this might work if you have FORMAT as an available function.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.