订单标记顶部带有匹配项

Question

I fetch tags from a database and I want to sort the tags that have matches in the table tag_posts at the top of the result. It is close to working, but I get duplicates because of the group by c_p_id. But if I remove c_p_id from the group by the order by sometimes fetches the wrong row. Would it make more sense to use some IF EXIST option?

I want the tags to be listed like this:

TAG B
TAG A
TAG C

If TAG-B has a hit in tag_posts.

SELECT c_p_id, t_id, t_title
FROM tags
LEFT JOIN projects
ON p_id = " . $p_id . "
LEFT JOIN tag_posts
ON tp_t_id = t_id
LEFT JOIN cats
ON c_id = tp_c_id
AND c_p_id = p_id
GROUP BY t_id, c_p_id
ORDER BY c_p_id DESC, t_title ASC

// EDIT. I figured out a different solution that is doing what I want:

SELECT t_id, t_title, 
    (SELECT 1 FROM tag_posts
        INNER JOIN cats
            ON c_id = tp_c_id
        INNER JOIN projects
            ON p_id = c_p_id
        WHERE tp_t_id = t_id
            AND p_id = " . $p_id . "
        LIMIT 1) used
FROM tags
ORDER BY used DESC, t_title ASC

Answer 1

Solution:

SELECT t_id, t_title, 
    (SELECT 1 FROM tag_posts
        INNER JOIN cats
            ON c_id = tp_c_id
        INNER JOIN projects
            ON p_id = c_p_id
        WHERE tp_t_id = t_id
            AND p_id = " . $p_id . "
        LIMIT 1) used
FROM tags
ORDER BY used DESC, t_title ASC