[thelist] method to remove/skip duplicates in one-to-many relationship
Chris W. Parker
cparker at swatgear.com
Mon Aug 11 13:34:35 CDT 2003
Hi.
Anyway to remove or just use the first duplicate in a one-to-many query?
With two tables like this:
products:
id | name
---------------------
prod001 | Product 001
prod002 | Product 002
products_masids
prodid | masid*
---------------
prod001 | sf001
prod001 | rx92x
prod001 | sr991
prod002 | abcde
prod002 | fghij
Using the following query:
SELECT p.id, p.name, pm.masid
FROM products AS p
INNER JOIN products_masids AS pm
ON pm.prodid = p.id
WHERE p.id LIKE '%$text%'
OR p.name LIKE '%$text%'
OR pm.masid LIKE '%$text%'
If I search for "prod001" I'd get the following returned:
id | name | masid
-------------------------------
prod001 | Product 001 | sf001
prod001 | Product 001 | rx92x
prod001 | Product 001 | sr991
If I search for "sf001" I'd get the following returned:
id | name | masid
-------------------------------
prod001 | Product 001 | sf001
The reason I am allowing each product to be put into multiple categories
is because of our print catalog. Some products are qualified to be in
more than one category (I should note that this happens to very few
products and they are not in more than two categories).
To make a long story short, what's the best way to display/return only
the first occurence of prod001?
Thanks,
Chris.
More information about the thelist
mailing list