derped derped - 4 months ago 9
SQL Question

MySQL/MariaDB - query Searching in table via a reference table

I currently have three tables:

desc products;
+----------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------------+------+-----+---------+----------------+
| id | int(255) unsigned | NO | PRI | NULL | auto_increment |
| name | text | NO | | NULL | |
| desc_short | text | NO | | NULL | |
+----------------+-------------------+------+-----+---------+----------------+

desc tags;
+------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+----------------+
| id | int(255) unsigned | NO | PRI | NULL | auto_increment |
| tag | varchar(255) | YES | UNI | NULL | |
| iscategory | tinyint(4) | NO | | 0 | |
+------------+-------------------+------+-----+---------+----------------+

desc products_tags;
+------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| product_id | int(255) unsigned | YES | MUL | NULL | |
| tag_id | int(255) unsigned | YES | MUL | NULL | |
+------------+-------------------+------+-----+---------+-------+


products_tags is actually a reference table that I've created with:

CREATE TABLE product_tags (product_id INT(255) UNSIGNED, tag_id INT(255) UNSIGNED, CONSTRAINT fk_products_id FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_tag_id FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE);


I'm trying to filter items from the table products by searching for a corresponding tag.
I have already found some similar problems but I couldn't get it to work properly....

Thanks a lot in advance.

Answer

Try this join query:

SELECT p.id, p.name, p.desc_short
FROM products p
INNER JOIN products_tag pt
    ON p.id = pt.product_id
INNER JOIN tags t
    ON t.id = pt.tag_id
WHERE t.tag = 'some tag'
Comments