hi Martin,
same set of IDs for all products. products may have multiple and more tag-IDs,
but i will be looking for only the given set of IDs - for all the products. I
could not figure to translate that SQL into DBIx syntax.
thank you.Rajeev
On Thursday, September 1, 2016 3:37 AM, Martin Hall
<[email protected]> wrote:
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:
SELECT a.*
FROM products a
INNER JOIN product_tags b ON a.product_id = b.product_id
WHERE b.tag_id IN (1,23,54)
GROUP BY a.product_id
HAVING COUNT(1) = 3 thank you. Rajeev
--
cheers
Martin
|
|
