CODEWITHSUNDEEP
mysqlsqlselect
LamKat
LamKat

Reputation: 1

MySQL Tag based system

I'm currently writing a basic search system for a music website, the system searches using tags('Instrumental Duo', 'Instrumental Trio', 'latin', ..ect). I'm trying to work out how to search the MySQL Database using the tags to limit the results.

The tables are meant to be related like;

| Items    |            | Tags     |
|----------|            |----------|
| PK_Items | One - Many | FK_Items |
| Title    |            | PK_Tags  |
| Writer   |            | Tag_txt  |

Each item in the table Items will have multiple items in the table Tags

In short I want to be able to give the database a collection of tags, and have it return all the items that 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 I wanted to search for "Slow", i should have all items with the 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 I searched for "Slow" AND "Rock", I would get

+----------+-------------+------------------+
| <b>PK_Items</b> | <b>Title</b>       | <b>Writer</b>           |
+----------+-------------+------------------+
|        3 | Song3       | Jane Smith       |
+----------+-------------+------------------+

I've tried 

SELECT *
FROM Items a
JOIN Type t ON t.FK_Items = a.PK_Items
WHERE t.Tag_txt = 'Slow';

Which gives (basically) what it should give, however when using "Slow" AND "Rock" i get a empty table.

SELECT *
FROM Items a
JOIN Type t ON t.FK_Items = a.PK_Items
WHERE t.Tag_txt = 'Slow' AND t.Tag_txt = 'Rock';

Please Help

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this using a having clause. Here is one way:

SELECT i.*
FROM Items i JOIN
     Type t
     ON t.FK_Items = i.PK_Items
WHERE t.Tag_txt IN *'Slow', 'Rock')
GROUP BY i.PK_Items
HAVING COUNT(DISTINCT t.tag_txt) = 2;

Upvotes: 1

Related Questions