Where clause not working as expected with junction table?

Question

I have a junction table that creates a many-to-many relationship between two other tables. Here is the diagram.

Here is my base SQL query.

SELECT `tag_to_url`.url_id, `websites`.url, `tags`.tag_name 
FROM `tag_to_url` 
INNER JOIN (`websites`,`tags`) ON `websites`.url_id = `tag_to_url`.url_id 
AND `tag_to_url`.tag_id = `tags`.tag_id 
ORDER BY `tag_to_url`.url_id

This is an example result from that.

+--------------------+-------------+
|         url        |   tag_name  |
+--------------------+-------------+
|     google.com     |    search   |
|     google.com     |    e-mail   |
| stackexchange.com  |     q&a     |
| stackexchange.com  | programming |
| stackexchange.com  |   database  |
+--------------------+-------------+

Those results are basically what I am looking for, although I've tried to group the URL which doesn't work correctly. The query will also work properly if I include one WHERE clause.

...
...
WHERE `tags`.tag_name = 'database'

+--------------------+-------------+
|         url        |   tag_name  |
+--------------------+-------------+
| stackexchange.com  |   database  |
+--------------------+-------------+

But, when I add the AND operator to the WHERE condition, I am expecting this.

WHERE `tags`.tag_name = 'database' AND `tags`.tag_name = 'q&a'

+--------------------+-------------+
|         url        |   tag_name  |
+--------------------+-------------+
| stackexchange.com  |   database  |
| stackexchange.com  |     q&a     |
+--------------------+-------------+

However, I get:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0027 sec)

I've also tried adding the condition in ON with the JOIN but the result was the same. What am I doing wrong please?

EDIT :

The purpose of this query is to fetch one or more tags along with a list of URL's associated with them. I need to be able to use multiple AND operators to retrieve only those specific tags. It needs to work like stack overflow, where there is a question (URL) and multiple tags attached to it.

Answer 1

The correct way to use AND OR in mysql is

select * from `table` where col1 = 'someval' and col2='someval' ;

select * from `table` where col1 = 'someval' OR col1='someval' ;

You need to specify the column names in each condition.

So you need to have the query as

WHERE `tags`.tag_name = 'database' OR `tags`.tag_name 'q&a'

Also the equivalent statement could be IN

WHERE `tags`.tag_name IN('database','q&a')

EDIT : Here is the query that might do the job by using group_concat and group by

SELECT 
tu.url_id, 
w.url, 
group_concat(t.tag_name) as tag_name
FROM tag_to_url tu
inner join websites w on w.url_id = tu.url_id 
inner join tags t on t.tag_id = tu.tag_id
where t.tag_name in ('database','q&a')
group by w.url
having count(*) = 2

Answer 2

You need to use OR instead of AND in the where clause since you are looking for both database and q&a.

so the where condition should be like WHEREtags.tag_name = 'database' OR 'q&a'