sql - MySQL Tag based system -

i'm writing basic search system music website, system searches using tags('instrumental duo', 'instrumental trio', 'latin', ..ect). i'm trying work out how search mysql database using tags limit results.

the tables meant related like;

| items    |            | tags     | |----------|            |----------| | pk_items | 1 - many | fk_items | | title    |            | pk_tags  | | writer   |            | tag_txt  | 

each item in table items have multiple items in table tags

in short want able give database collection of tags, , have return items match

---

example data

the tables have been created using

create table items (     pk_items int not null auto_increment,     title text,     writer varchar(30),     primary key(pk_items) ); create table tags (     pk_tags int not null,     fk_items int not null,     tag_txt varchar(100),     primary key(pk_type),     foreign key(fk_items) references items(pk_items) ); 

sample data

+----------+-------------+------------------+ | <b>pk_items</b> | <b>title</b>       | <b>writer</b>           | +----------+-------------+------------------+ |        1 | song1       | joe blogs        | +----------+-------------+------------------+ |        2 | song2       | joe blogs        | +----------+-------------+------------------+ |        3 | song3       | jane smith       | +----------+-------------+------------------+  +----------+-------------+------------------+ | <b>pk_tags</b>  | <b>fk_items</b>    | <b>tag_txt</b>          | +----------+-------------+------------------+ |        1 |           1 | slow             | +----------+-------------+------------------+ |        2 |           1 | jazz             | +----------+-------------+------------------+ |        3 |           2 | fast             | +----------+-------------+------------------+ |        4 |           2 | rock             | +----------+-------------+------------------+ |        5 |           3 | slow             | +----------+-------------+------------------+ |        6 |           3 | rock             | +----------+-------------+------------------+ 

if wanted search "slow", should have items slow tag displaying e.g.

+----------+-------------+------------------+ | <b>pk_items</b> | <b>title</b>       | <b>writer</b>           | +----------+-------------+------------------+ |        1 | song1       | joe blogs        | +----------+-------------+------------------+ |        3 | song3       | jane smith       | +----------+-------------+------------------+ 

or if searched "slow" , "rock", get

+----------+-------------+------------------+ | <b>pk_items</b> | <b>title</b>       | <b>writer</b>           | +----------+-------------+------------------+ |        3 | song3       | jane smith       | +----------+-------------+------------------+ 

---

i've tried

select * items join type t on t.fk_items = a.pk_items t.tag_txt = 'slow'; 

which gives (basically) should give, when using "slow" , "rock" empty table.

select * items join type t on t.fk_items = a.pk_items t.tag_txt = 'slow' , t.tag_txt = 'rock'; 

---

please help

you can using having clause. here 1 way:

select i.* items join      type t      on t.fk_items = i.pk_items t.tag_txt in *'slow', 'rock') group i.pk_items having count(distinct t.tag_txt) = 2;