Join Query returns empty result, unexpected result

Question

Hello all,

Can anyone explain why this query returns an empty result.

SELECT *
FROM (`bookmarks`)
JOIN `tags` ON `tags`.`bookmark_id` = `bookmarks`.`id`
WHERE `tag` = 'clean'
AND `tag` = 'simple'

In my bookmarks table, I have a bookmark with an id of 70 and in my tags table i have two tags 'clean' and 'simple' both that have the column bookmark_id as 70. I would of thought a result would have been returned?

How can I remedy this so that I have the bookmark returned when it has a tag of 'clean' and 'simple'?

Thanks all for any explanation and solution to this.

Update

My tag table holds many tags. A bookmark can have many tags. id in the bookmarks table and the bookmark_id in the tags table are linked.

Answer 1

It's unlikely that there's a row whose tag equals both 'clean' and 'simple' :)

So try replacing AND with OR:

WHERE `tag` = 'clean'
OR `tag` = 'simple'

If you intend to retrieve only bookmarks with both tags, consider a double exists clause:

SELECT *
FROM bookmarks b
WHERE EXISTS (
    SELECT *
    FROM tags t
    WHERE t.tag = 'simple'
    AND t.bookmark_id = b.id
) AND EXISTS (
    SELECT *
    FROM tags t
    WHERE t.tag = 'clean'
    AND t.bookmark_id = b.id
)

It's also possible to check using a having statement:

SELECT    b.id
FROM      bookmarks b
JOIN      tags t
ON        t.bookmark_id = b.id
          AND t.tag in ('clean','simple')
GROUP BY  b.id
HAVING    COUNT(distinct t.tag) = 2

The count will ensure both tags are found.

Answer 2

You get no rows because your where clause is impossible to satisfy.

To get bookmarks that have both a tag 'clean' and a tag 'simple' you need to join with the tags table twice. Try doing it like this:

SELECT bookmarks.*
FROM bookmarks
JOIN tags AS T1 ON T1.bookmark_id = bookmarks.id AND T1.tag = 'clean'
JOIN tags AS T2 ON T2.bookmark_id = bookmarks.id AND T2.tag = 'simple'

Answer 3

Select ...
From `bookmarks`
Where Exists(
            Select 1
            From `tags`
            Where `tags`.`bookmark_id` = `bookmarks`.`id`
                And 'tag' In('clean','simple')
            Having Count(*) = 2
            )

(BTW, I'm assuming that a bookmark cannot have a given tag twice).