How to limit duplicate column results in a MySQL query?

I'm having difficulty removing the duplicate data when exporting an order where more than 1 product is ordered.

Example:

orders_id, product_id, customer_first_name, customer_last_name
001     , ProductA  , FirstName          , LastName
001     , ProductB  , FirstName          , LastName
001     , ProductC  , FirstName          , LastName
002     , ProductA  , FirstName          , LastName
003     , ProductB  , FirstName          , LastName
003     , ProductC  , FirstName          , LastName
004     , ProductA  , FirstName  开发者_如何学Go        , LastName

I need to remove the duplicating data, like so:

orders_id, product_id, customer_first_name, customer_last_name
001     , ProductA  , FirstName          , LastName
        , ProductB  ,                    ,          
        , ProductC  ,                    ,          
002     , ProductA  , FirstName          , LastName
003     , ProductB  , FirstName          , LastName
        , ProductC  ,                    ,          
004     , ProductA  , FirstName          , LastName

I tried DISTINCT, without any change.

Assuming the query to extract the above is as follows:

SELECT 
o.orders_id
, op.product_id
, o.customer_first_name
, o.customer_last_name
FROM orders AS o
, orders_products AS op
WHERE o.orders_id = op.orders_id;

How would I limit the data from duplicating?

Much appreciated!

Peace, Chris

---

The problem is with how your order_products table is constructed. Due to the fact that the order_id is duplicated several times in that table you'll return equally many rows whenever you join against order_id

I'm assuming the table looks something like this:

order_id       product_id
001            ProductA
001            ProductB
001            ProductC

Which will always return 3 rows for every product_id with the current query.

You're probably better of returning a flattened "Products" field that concatinates the product_ids together using the Group_Concat command:

SELECT o.orders_id
    ,GROUP_CONCAT(DISTINCT op.product_id SEPARATOR ', ')
FROM orders AS o
, orders_products AS op
WHERE o.orders_id = op.orders_id
GROUP BY o.orders_id;

This will return something similar to the following:

order_id       products
001            ProductA, ProductB, ProductC

---

This is a display problem and not an SQL one. It could be solved with SQL but the query would be rather horrible (in MySQL).

One way to solve is to change the display a bit by using @Bauhaus advice and GROUP_CONCAT() function.

Another way would be to change the application code to combine the results of two queries:

--- Get orders
SELECT 
    o.orders_id
                                         --- REMOVED: op.product_id
  , o.customer_first_name
  , o.customer_last_name
FROM orders AS o
ORDER BY o.orders_id ;                   --- Order both queries by orders_id
                                         --- to help the application code 
                                         --- match related data

--- Get order details (products)
SELECT 
    o.orders_id
  , op.product_id
                                         --- REMOVED: o.customer_first_name
                                         --- REMOVED: o.customer_last_name
FROM orders AS o
  JOIN orders_products AS op             --- Don't use WHERE for joining tables,
    ON o.orders_id = op.orders_id        --- use the JOIN ... ON syntax
ORDER BY o.orders_id ;


--- Combine results
/* application code */

---

Check out DISTINCT on o.orders_id when doing your select statement. http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Update..sorry I didn't see you had tried distinct already. Maybe try GroupBy http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html and group by the order_id.

---

If I understand what you're asking, you're still trying to get back a complete list of all products from all orders.

You basically have two options:

Send lots and lots of queries at the server, using a nested loops:

1. Get a listing from the orders table
2. For each record, ask for the associated records from the orders_products table.
3. Print the first line w/ the merged info from the first correlated record
4. Loop through the rest of the results, printing only the product information

What's often more efficient is to just get it all, then use some if/then logic:

1. initialize a variable to track the last order_id.
2. loop through the merged list (what you started with)
3. if the current order_id isn't the same as the last order_id, print the full record ... otherwise, only print the product info.
4. update the last order_id w/ the current order_id.

It's possible that PHP might have a way to specifically handle the second case. It's been years since I used it, but it was the recommended way in ColdFusion ... look at the 'group' argument to 'cfoutput'.

---

You don't have any duplicate results, your getting exactly what your asking for.

Since and order can have many products you are getting all the products that belong to an order but the order id will always be duplicated, there is no way in relational databases to get it all in a single row unless you flatten your results.