"noone" <no***@nowhere. com> wrote in message
news:f2******** *************** *********@www.f irstdbasource.c om...
sks wrote:
I have a table that contains keywords (Varchars) each one mapped to a
product. so the database schema is just an id column, product column and
keyword column.
I want to select the products that contain x,y,z keywords. Now if this
query
involves many keywords I end up with a massive amount of self joins on
the
keywords table, is there a better way to do this than self joins? What
would
work perhaps is multiple unions where I could discard non duplicated rows
but you can't seem to do that. Any ideas... ?
Show us the queries you have come up with and why they don't work and
provide some sample data (does not have to be real data).
Is there more than one table involved?
What do you consider "many keywords"?
Why are you doing "self-joins"?
Do the keywords map to more than one product?
Is this for a school project or some job that you have?
It's for a software package I write.
The keywords are used as meta data about an item. Each of the keywords is
for a particular attribute that is relevant for certain items only. This is
all definable by the user at runtime. Let's say we had a site selling DVDs,
the attributes in this case might be Director, Actor, and Genre. Each of
these attributes might have multiple values (or none).
Here is a simple schema to demonstrate
Items
--------------
id
name
Attributes
----------
id
name
Attributes_Valu es
----------
id
item
value
I might want to find all DVDs that had Clint Eastwood, or Tommy Lee Jones,
or Richard Burton in as Actor, and where Comedies, Action or Drama in Genre.
I could self join like this
select distinct i.* from item i join attributes_valu es av1 on i.id=av1.item
join attributes_valu es av2 on i.id=av2.item where (av1.value='Cli nt
Eastwood' or av1.value='Tomm y Lee Jones' ......) and av2.value='Come dy' or
av2.value='Dram a' ....) where i.status='Live' .... (other item related
clauses here).
This runs extremely slowly when I have more than a few attributes. 9 or 10
is quite common on some items and then you could be searching for 5 to 10
values in each attribute.
My faster solution (and the one I use ) is to select the item column from
the attribute_value s table and union it for each different attribute I am
searching against, then group that result and select from it where count is
equal to the number of attributes I searched for and then join that against
the item table. It's fast but I think ugly.
I am wondering if there is an obvious solution I have missed.
