MySQL UNION show duplicates in DataTables

Question

This is driving me crazy. I have two tables which contain invoice data. I have a customer who has one unpaid invoice in each table. I'm using this code to join the data from the two tables and get the two invoice rows:

$stmt = $db->prepare ("

    SELECT invoice_id, invoice_number, customer_id, paid 
    FROM 
    ( 
      SELECT invoice_id, invoice_number, customer_id, paid FROM tbl_invoices
      UNION ALL 
      SELECT invoice_id, invoice_number, customer_id, paid FROM tbl_invoices_el
    ) 
    tbl_invoices
    WHERE customer_id = $customer_id 
    AND paid = '0'
    GROUP BY invoice_number
");

The code works fine in phpMyAdmin, the result is two rows with the data. On the page I'm using Bootstrap + DataTables to show the result in a modal window. The strange: no matter how I try, the first invoice is repeated two times. As I said before the code works fine in phpMyAdmin, the var_dump() of the fetched result is showing two arrays, as expected. What I'm doing wrong here?

UPDATE The $output .=""wasn't empty. My fault. @Barmar discovered the issue.

$stmt->execute();
$all_result = $stmt->fetchAll();
$total_rows = $stmt->rowCount();
if ( $total_rows > 0 )  
{
   foreach ($all_result as $row )  
     { 
     /* $output .=" ........... removed */
      echo " 
      <tr style='font-size:13px;'>
        <td width='5%'>
            <div class='form-check' style='margin-bottom:0px'>
                <label class='form-check-label'>
                  <input data-attr='" .$row['invoice_number']. "' id='" .$row['invoice_id']. "' class='form-check-input' type='checkbox' value='" .$customer_id. "'> 
                  <span class='form-check-sign'>
                    <span class='check'></span>
                  </span>
                </label>
            </div>
        </td>
        <td width='15%'>".$row['invoice_number']."</td>
      </tr>   
    ";
    /* echo $output; ........... removed */
  }
}

The result of var_dump():

GROUP BY without aggregation is almost always a mistake, what are you expecting GROUP BY to do? Also, since that use of GROUP BY guarantees you would not have duplicate invoice_number values, I can only conclude/guess the problem is in the php loop you are have omitted from the code. — Uueerdo, Feb 05 '19 at 21:29
If only MySQL would implement `DISTINCT ON (invoice_number), invoice_id customer_id, paid` (PostgreSQL syntax) to unduplicate in MySQL/MariaDB you can emulate/simulate [that PostgreSQL syntax (post off me)](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691).. — Raymond Nijland, Feb 05 '19 at 21:38
But for better help i advice you to post example data as text formatted data (no images) and expected result so you get better help. See [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Raymond Nijland, Feb 05 '19 at 21:41
@Uuerdo - You're right removing `GROUP BY invoice_number` doesn't change the output I updated my code with the loop. Just before the data goes in the DataTables the output is correct. — uomopalese, Feb 05 '19 at 22:10
@Uueerdo - Good idea to suggest me about the omitted php loop, somehow the solution comes from your comment. Unfortunally I can't upvote a comment. Thanks. — uomopalese, Feb 05 '19 at 22:43

score 1 · Accepted Answer · answered Feb 05 '19 at 22:18

There's no way you can get duplicate invoice numbers when you have GROUP BY invoice_number, so the code you showed can't produce that duplicate.

I suspect the problem is that you didn't clear $output, and there's some previous output with the first invoice number. Put:

$output = "";

before the foreach loop.

MySQL UNION show duplicates in DataTables

1 Answers1