[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