MySQL Forums
Forum List  »  Newbie

Is there a way to handle necessary duplicate values?
Posted by: sudden zero
Date: May 19, 2016 03:41PM

So I have a database that has four tables:

1. products (contains all products)
2. products_suppliers (linking table between the products table and the suppliers table.)
3. suppliers (contains all suppliers)
4. categories (contains the categories that the products belong to)

Some of the products have the same name because they are the same thing but from different suppliers. All product names are appended with a product number (which contains two letters and four numbers) at the end of the name
to prevent name duplication. However MySQL seems to still treat these as duplicates. Example


product 1: Chocolate Donut
Supplier: Donut Co
Product# DCO5678
product_name = Chocolate Donut DCO5678

product 2: Chocolate Donut
Supplier: Donut Heaven
Product# DH4532
product_name = Chocolate Donut DH4532



I am using the following query to get the necessary information to write a report. However the items with duplicate names
(minus the appended product number) seem to still be treated as duplicates and are not being displayed.


SELECT a.product_id, a.product_name, a.product_number, a.supplier_sku, a.measurement,
a.depleated, d.category_name, c.supplier_id, c.name
FROM products a, products_suppliers b, suppliers c, categories d
WHERE a.product_id = b.fk_product_id
AND c.supplier_id = b.fk_supplier_id
AND a.fk_category_id = d.category_id
AND a.depleated <> 1
GROUP BY a.product_name
ORDER BY product_id ASC

Any help would be greatly appreciated. Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Is there a way to handle necessary duplicate values?
May 19, 2016 03:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.