Don't think this counts as a DBI question really as the best solution
would be to do it all in a single piece of SQL, rather than bringing the
data back for local processing. You're not clear in your description
whether you are looking for the same set of tag IDs for every product or
different sets. If it's the former, then you have a solution. If it's
the latter you need to be clear on how you will know that a product has
the complete set of tags and that will likely require some form of
additional lookup table.
On 01/09/2016 04:06, Rajeev Prasad via dbi-users wrote:
Pl refer to this link, it has my exact problem (but it only tells
about SQL solution):
http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations
I have three tables:
1. products:-> prod-id, prod-name
2. tags:-> tag-id, tag-name
3. product_tags::-> prod-id, csv_tag_id
i read elsewhere on internet that this is not a good database/table
design. so i am ready to change that too, but i could not think of any
other way to represent this relationship. a given product could have
multiple tags, but i have to find - at a given time - only those
products which have 'ALL' of multiple given tags.
the SQL suggested on the page is:
|SELECTa.*FROMproducts a INNERJOINproduct_tags b ONa.product_id
=b.product_id WHEREb.tag_id IN(1,23,54)GROUPBYa.product_id
HAVINGCOUNT(1)=3|
thank you.
Rajeev
--
cheers
Martin
