I created a database that stores details of YouTube videos. There are 5 primary tables involved. A video table, a channel table, a category table, a tag table, and a video_tag table. The table structures are as follows:
+--------------+ +----------+ +----------+
| video | | channel | | category |
+--------------+ +----------+ +----------+
| id | | id | | id |
| channel_id | | title | | title |
| category_id | +----------+ +----------+
| title |
| ... |
+--------------+
+--------------+ +------+
| video_tag | | tag |
+--------------+ +------+
| video_id | | id |
| tag_id | | name |
+--------------+ +------+
What I am trying to do is get all the videos where the video title, channel title, category title, and/or tag name match a certain search phrase. I am trying to accomplish this using the following query:
SELECT DISTINCT v.youtube_id v_youtube_id,
ch.youtube_id ch_youtube_id, ch.title ch_title,
ca.youtube_id ca_youtube_id, ca.title ca_title,
dtl.id dtl_id, dtl.code dtl_code, dal.id dal_id, dal.code dal_code,
v.youtube_date_time_published, v.title v_title, v.description, v.youtube_thumbnail_url, v.duration, v.views_count, v.likes_count, v.dislikes_count
FROM video v
JOIN channel ch ON v.channel_id = ch.youtube_id
JOIN category ca ON v.category_id = ca.youtube_id
LEFT OUTER JOIN language dtl ON v.default_text_language_id = dtl.id
LEFT OUTER JOIN language dal ON v.default_audio_language_id = dal.id
LEFT OUTER JOIN video_tag vt ON v.youtube_id = vt.video_id
LEFT OUTER JOIN tag t ON vt.tag_id = t.id
WHERE ca.youtube_id = 10 AND
v.title LIKE "%jazz%" OR v.title LIKE "%mix%" OR
ch.title LIKE "%jazz%" OR ch.title LIKE "%mix%" OR
ca.title LIKE "%jazz%" OR ca.title LIKE "%mix%" OR
t.name LIKE "%jazz%" OR t.name LIKE "%mix%"
There's an extra language table in the query where the value is NULL most of the time. Also, I know I'm using OR in the query conditions, but I do my own consolidation to make sure the entire query "jazz mix" is matched on the application side.
This query appears to be working for the most part in matching the search phrase, though I'm not sure what the best way is to test every single result. Anyhow, the problem I'm facing is that even though I want to get only results with category = 10, I'm also getting a few results where the category is not 10, and I'm not sure where my mistake is in the query.
Also, if you see any obvious ways in improving my query, please let me know as well.
