MySQL query with multiple conditions not working

Question

I have several tables:

videos

id
ch_id
title
date_published

channels

id
title

tags

id
title

video_tags

v_id
t_id

Using those tables, I am trying to select videos related to another video using tags. In my current attempt, I've tried the following:

SELECT v.id v_id, ch.id ch_id, ch.title ch_title,
       v.title v_title, v.date_published v_date_published
    FROM videos v
    JOIN channels ch ON ch.id = v.ch_id
    JOIN video_tags vt ON vt.v_id = v.id
    WHERE v.id NOT LIKE 'example_id'
    AND (((vt.t_id = 1) + (vt.t_id = 2) + (vt.t_id = 3) + (vt.t_id = 4)) >= 2)
    GROUP BY v.id ORDER BY v.date_published LIMIT 10;

However, this does not work. I do not get errors, but I get 0 results back when I know there should be more results.

I've spent hours thinking about it and looking for answers online. Maybe I'm using the wrong words in my searches, but I haven't been able to find a solution. Any help would be greatly appreciated.

First you are assigning v to the table videos -- and so on .. These "should" be included in your SELECT -- IE SELECT v.v_id, ch.ch_id etc etc — Zak
– Zak, Commented Sep 6, 2017 at 22:11
why did you have this? AND (((t_id = 1) + (t_id = 2) + (t_id = 3) + (t_id = 4)) >= 2) — Ferdinand Gaspar
– Ferdinand Gaspar, Commented Sep 6, 2017 at 22:14
It would help if you clarify exactly what you're trying to do. I think you're trying to select a video if it has at least two tags from a specific set of four, is that right? (Please edit the question, don't reply with a comment.) The way you're trying to do that unfortunately isn't going to work. The reason I'm answering in a comment rather than an answer is that it's not immediately obvious to me how to do what you want in a SQL query. If the database is small you may want to consider doing it in app code instead. — Glenn Willen
– Glenn Willen, Commented Sep 6, 2017 at 22:15
@Zak that's how it is in my code, I was just trying to simplify it for the question, but I fixed it in the question now. — user4181107
– user4181107, Commented Sep 6, 2017 at 22:17

Barmar · Accepted Answer · 2017-09-06 22:46:10Z

1

The problem is that you're doing the addition of all the vt.t_id = X comparisons in the WHERE clause. That gets executed for one row at a time, it doesn't operate on the aggregate of all rows in each group. You should do it in a HAVING clause, using SUM() to combine all the rows.

SELECT v.v_id, ch.ch_id, ch.ch_title, v.v_title, v.v_date_published
FROM videos v
JOIN channels ch ON ch.ch_id = v.ch_id
JOIN video_tags vt ON vt.v_id = v.v_id
WHERE v.v_id != 'example_id'
GROUP BY v.v_id 
HAVING SUM(vt.t_id IN (1, 2, 3, 4)) >= 2
ORDER BY v.v_date_published LIMIT 10;

answered Sep 6, 2017 at 22:46

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

vt.t_id IN (...) is 1 if the ID is in the list, 0 otherwise. It sums these results for all the rows in the group for v.v_id. Then HAVING keeps the row in the result set if the sum is at least 2.

Zak · Accepted Answer · 2017-09-06 22:21:05Z

0

One, your selectors are wrong in the SELECT statement, your AND statement, your JOIN statement, and your GROUP BY statement. -- IE v.v_id

Two, your LIKE statement is likely failing without the % parameter

SELECT v.v_id, ch.ch_id, ch.ch_title, v.v_title, v.v_date_published
FROM videos v
JOIN channels ch ON ch.ch_id = v.ch_id
JOIN video_tags vt ON vt.v_id = v.v_id
WHERE v.v_id NOT LIKE '%example_id%'
AND (((vt.t_id = 1) + (vt.t_id = 2) + (vt.t_id = 3) + (vt.t_id = 4)) >= 2)
GROUP BY v.v_id ORDER BY v.v_date_published LIMIT 10;

NOTE This is assuming your AND Statement is correct! I am just pointing out the syntactical errors!

answered Sep 6, 2017 at 22:21

Zak

7,5812 gold badges29 silver badges62 bronze badges

3 Comments

user4181107 Over a year ago

I fixed the selectors as per your earlier comment. As to your second point, it's an ID that I'm first selecting from the database in another query, then using that ID in this query, I don't think the % modifier is necessary there. Everything in my query works as intended without the second part of the WHERE clause, that is the part after the AND and before the GROUP BY.

Barmar Over a year ago

@user4181107 If you're not doing a pattern match, it should be != rather than NOT LIKE.

Barmar Over a year ago

However, MySQL is smart enough to notice that the value doesn't have any wildcards, so it will treat LIKE or NOT LIKE as if you'd written = or != in that case.

Ferdinand Gaspar · Accepted Answer · 2017-09-06 23:23:22Z

0

Use HAVING COUNT for the tag id instead

SELECT v.id v_id, 
       ch.id ch_id, 
       ch.title ch_title,
       v.title v_title, 
       v.date_published v_date_published
  FROM videos v
  JOIN channels ch 
    ON ch.id = v.ch_id
  JOIN video_tags vt 
    ON vt.v_id = v.id
 WHERE v.id NOT LIKE 'example_id'
 GROUP BY v.id 
HAVING COUNT(vt.t_id) >= 2
 ORDER BY v.date_published 
 LIMIT 10;

answered Sep 6, 2017 at 23:23

Ferdinand Gaspar

2,0831 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL query with multiple conditions not working

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related