MySQL根据多个表条件选择

Question

I am working on a quiz website and need to suggest a quiz the user may like based on the quiz they just took. Basically, if the quiz they just took has a tag I need to pull another quiz with the same tag. Otherwise, pull another quiz from the same category.

What I have is working 90% the way I want it to, but it seems very bulky to me. The 10% that's not working is I forgot to add a condition where it only pulls quizzes that are active. Something like WHERE q.active = 1 but no matter where or how I add that condition it does not work as planned. I either get an empty set or the same result I would was receiving before the condition was added.

EDIT:

To clarify, after reading the comment by @RiggsFolly...

If I add AND q.active = 1 to my first WHERE clause and there is only one other quiz with the same tag and it is not active I receive an empty set instead of receiving a result from the category select statement. Adding AND q.active = 1 to just the category select statement and not the tag select statement returns the proper result if there are no quizzes with the same tag.

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    1 istag
FROM tag_index t
LEFT JOIN tag_index ti 
    ON ti.tag_id = t.tag_id
LEFT JOIN quizzes q 
    ON q.id = ti.quiz_id
WHERE t.quiz_id = :quiz_id 
    AND ti.quiz_id != t.quiz_id

UNION ALL

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    0 istag
FROM category_index c
LEFT JOIN category_index ci 
    ON ci.category_id = c.category_id
LEFT JOIN quizzes q 
    ON q.id = ci.quiz_id
WHERE c.quiz_id = :quiz_id 
    AND ci.quiz_id != c.quiz_id 
    AND NOT EXISTS
        (SELECT 1 FROM tag_index WHERE quiz_id = :quiz_id)
ORDER BY RAND() LIMIT 1

Any help or suggestions would be greatly appreciated!

Answer 1

You also need to add the active = 1 condition into your subselect:

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    1 istag
FROM tag_index t
LEFT JOIN tag_index ti 
    ON ti.tag_id = t.tag_id
LEFT JOIN quizzes q 
    ON q.id = ti.quiz_id
WHERE t.quiz_id = :quiz_id 
    AND ti.quiz_id != t.quiz_id
    AND q.active = 1

UNION ALL

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    0 istag
FROM category_index c
LEFT JOIN category_index ci 
    ON ci.category_id = c.category_id
LEFT JOIN quizzes q 
    ON q.id = ci.quiz_id
WHERE c.quiz_id = :quiz_id 
    AND ci.quiz_id != c.quiz_id 
    AND q.active = 1
    AND NOT EXISTS
        (SELECT 1
         FROM tag_index t
         JOIN tag_index ON ti.tag_id = t.tag_id
         JOIN quizzes ON q.id = ti.quiz_id
         WHERE t.quiz_id = :quiz_id 
           AND ti.quiz_id != t.quiz_id
           AND q.active = 1)
ORDER BY RAND() LIMIT 1

However you don't need that subselect at all you can just order by istag first:

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    1 istag
FROM tag_index t
LEFT JOIN tag_index ti 
    ON ti.tag_id = t.tag_id
LEFT JOIN quizzes q 
    ON q.id = ti.quiz_id
WHERE t.quiz_id = :quiz_id 
    AND ti.quiz_id != t.quiz_id
    AND q.active = 1

UNION ALL

SELECT 
    IFNULL(q.meta_title, q.title) AS title, 
    IFNULL(url, title) AS url, 
    0 istag
FROM category_index c
LEFT JOIN category_index ci 
    ON ci.category_id = c.category_id
LEFT JOIN quizzes q 
    ON q.id = ci.quiz_id
WHERE c.quiz_id = :quiz_id 
    AND ci.quiz_id != c.quiz_id 
    AND q.active = 1
ORDER BY istag DESC, RAND() LIMIT 1