Join Query returns empty result, unexpected result
Can anyone explain why this query returns an empty result.
SELECT *
FROM (`bookmarks`)
JOIN `tags` ON `tags`.`bookmark_id` = `bo开发者_Python百科okmarks`.`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.
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.
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'
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).
精彩评论