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.

enter image description here

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.

score 1 · Answer 1 · answered Dec 07 '14 at 07:23

1

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'

answered Dec 07 '14 at 07:23

Vinay

61
6

Thanks, but I've tried that too. It gives only rows with the`database` tag. – EternalHour Dec 07 '14 at 07:26
I am not sure about MySQL but if it is similar to SQL Server, you should be using it like this `WHERE tags.tag_name = 'database' OR tags.tag_name = 'q&a'` check if this works – Vinay Dec 07 '14 at 07:35
I honestly don't know, I've tried everything I can think of and been researching this for days but it just won't work. Seems that the results are returned with the first `where` and it ignores the second. It may be an issue with the `JOIN` and they are not related properly. – EternalHour Dec 07 '14 at 07:38
Well I tried to work it out on SQLFiddle and it seems to work Can you reproduce your problem [here](http://sqlfiddle.com/#!2/0113e2/2) – Vinay Dec 07 '14 at 07:59
Hmmm that `OR` is actually returning something, but it returns all the records related to only `database` as well as the records related to `database` and `q&a`. I need a query that returns only records related to `database` and `q&a` not the tags individually. Which is why I was using the `AND` operator. – EternalHour Dec 07 '14 at 08:11
Ok now I understand your question. Try this -- `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 WHERE `websites`.url in ( SELECT `websites`.url 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 WHERE tags.tag_name = 'database' OR tags.tag_name = 'q&a' Group by `websites`.url having count(`tags`.tag_name) > 1 ORDER BY `tag_to_url`.url_id );` – Vinay Dec 07 '14 at 08:22
Yes that is correct, I will clarify it in the question. – EternalHour Dec 07 '14 at 08:22
That's a really interesting query. It's closer but it returns all tags related to the url that is related to either of those tags. So based on my first example it returns all rows for stackexchange.com. database, q&a, and programming. – EternalHour Dec 07 '14 at 08:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66347/discussion-between-vinay-and-eternalhour). – Vinay Dec 07 '14 at 08:49

Abhik Chakraborty · Accepted Answer · 2014-12-07T20:28:30.897

0

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

edited Dec 07 '14 at 20:28

answered Dec 07 '14 at 09:21

Abhik Chakraborty

44,654
6
52
63

It was a typo on my part and I have corrected it. Still returns no rows. – EternalHour Dec 07 '14 at 09:36
You are doing `AND` and this means get the row where `tag_name` is both `database` and `q&a` at the same time, and its not possible. Change `AND` to `OR` or `IN` – Abhik Chakraborty Dec 07 '14 at 09:41
I would suggest to have `where trim(tags.tag_name) = 'database' OR trim(tags.tag_name) = 'q&a'` or `trim(tags.tag_name) in ('database','q&a')` this will ensure that if there is white space then before search this will be taken care. Also while saving in some cases `&` could be saved in DB as `&` from the front end if its not done properly. – Abhik Chakraborty Dec 07 '14 at 09:47
I understand what you're saying. `AND` would only return something if those values were in the same row. But I need to be able to return any tags related to a specific URL. It does me no good to only find one or the other. – EternalHour Dec 07 '14 at 19:51
well in that case you need `group by` and `having count()`, let me create a fiddle – Abhik Chakraborty Dec 07 '14 at 19:58
Here is an example http://www.sqlfiddle.com/#!2/f2132/3, you can group them and by checking if both of tags are matching. – Abhik Chakraborty Dec 07 '14 at 20:07
Yes, that is exactly what I am trying to do. However, how to make it work with my existing db schema and query? The fiddle is only one table. – EternalHour Dec 07 '14 at 20:17
You've done it! Thanks! Can I order the `group_concat`? – EternalHour Dec 07 '14 at 20:39
1

yes you can do as `group_concat(t.tag_name order by t.tag_name)` – Abhik Chakraborty Dec 07 '14 at 20:41
1

You made my week Abhik :) – EternalHour Dec 07 '14 at 20:43

Where clause not working as expected with junction table?

2 Answers2