Join 4 tables with 2 one to one relationships

Question

The main table "video_index" contains foreign keys for "author_index" and "category_index".

And the "feature_main_rel" table contains the foreign key for "video_index"

I want to join all of these together so that only valid videos with valid authors & categories that are within the feature period is shown. I've tried the following query but got an error

Query:

SELECT
    *
FROM
    author_index AS a JOIN (
        SELECT
            *
        FROM
            video_index
        JOIN
            feature_main_rel
        ON
            video_index.id = feature_main_rel.video_id
    ) AS fv
        ON a.id = fv.author_id
    JOIN category_index AS c
        ON fv.category_id = c.id
WHERE
    video_index.remove = '0' AND
    video_index.active = '1' AND
    video_index.publish_start <= '$current_time' AND
    video_index.publish_end >= '$current_time' AND
    author_index.remove = '0' AND
    author_index.active = '1' AND
    category_index.remove = '0' AND
    category_index.active = '1' AND
    feature_main_rel.remove = '0' AND
    feature_main_rel.active = '1' AND
    feature_main_rel.start <= '$current_time' AND
    feature_main_rel.end >= '$current_time'
GROUP BY
    video_index.id
ORDER BY
    RAND()
LIMIT
    1

Error:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

Answer 1

http://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

Any columns in the subquery select list must have unique names.

So you should name the columns in your subselect explicitly, as there seems to be a id-column which then collides with the outer select *