How can I 'weight' a fulltext search on one table (podcasts_episodes) by a field (weight) in another table (podcasts) that coresponds to the id?
An example of both tables:
mysql> describe podcasts;
+--------------+--------------+
| Field | Type |
+--------------+--------------+
| id | int(12) |
| name | varchar(200) |
| weight | float |
+--------------+--------------+
mysql> describe podcasts_episodes;
+-------------+--------------+
| Field | Type |
+-------------+--------------+
| id | int(12) |
| name | varchar(200) |
| link | varchar(255) |
| description | text |
| podcast | int(12) |
| weight | float |
+-------------+--------------+
Rather than having to set a weight for each episode, it would be far simpler to use a common one in the podcasts table.
In the podcasts_episodes table, the field 'podcast' matches the id in the podcsts table for the podcast that episode belongs to.
The sql query I have been using, which needs updated as described is:
SELECT
*, MATCH(name,description) AGAINST('industrial') AS relevance
FROM
podcasts_episodes
WHERE
MATCH(name,description) AGAINST('industrial')
ORDER BY
weight DESC, relevance DESC
LIMIT 10
The expected outcome should be that only relevant results show up, but results weighted higher should get a boost above lower weighted results.
