antoyo antoyo - 9 days ago 4
SQL Question

How to select all the related fields and filtering the values by these fields

I have the following many-to-many relationship:

CREATE TABLE IF NOT EXISTS bookmarks (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
url TEXT NOT NULL UNIQUE
)

CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
)

CREATE TABLE IF NOT EXISTS bookmarks_tags (
bookmark_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
FOREIGN KEY(bookmark_id) REFERENCES bookmarks(id),
FOREIGN KEY(tag_id) REFERENCES tags(id)
)


I wonder how to do a query to filter bookmarks by tags, but still select all tags.

I have the following query:

SELECT title, url, GROUP_CONCAT(tags.name)
FROM bookmarks
LEFT OUTER JOIN bookmarks_tags
ON bookmarks.id = bookmarks_tags.bookmark_id
LEFT OUTER JOIN tags
ON bookmarks_tags.tag_id = tags.id
WHERE tags.name = "tag1"
GROUP BY url


but if a bookmark has more than one tag, only
tag1
will get returned.

And adding
AND tags.name = "tag2"
in the
WHERE
clauses will return no results.

So how to select all tags while filtering the bookmarks by tags?

Answer

You are looking for bookmark having at least one tag1 record. You can check this after aggregation in a having clause.

SELECT b.title, b.url, GROUP_CONCAT(t.name)
FROM bookmarks b
JOIN bookmarks_tags bt ON b.id = bt.bookmark_id
JOIN tags t ON bt.tag_id = t.id
GROUP BY b.url
HAVING COUNT(CASE WHEN t.name = 'tag1' THEN 1 END) > 0;

Outer joins make no sense here, because you are only interested in bookmarks that do have tags.

Comments