XF 1.4 Query to find unassigned prefixes?

[Kevin]

After the dust has settled of doing a few forum merges I'm cleaning up stuff.

Can anybody suggest a query to run to find prefixes that are not assigned to any forums?

 

[Chris D]

I think this should do it:

SELECT *
FROM xf_thread_prefix AS tp
WHERE (
    SELECT prefix_id
    FROM xf_forum_prefix AS fp
    WHERE fp.prefix_id = tp.prefix_id
) IS NULL

It should return a list of all prefixes that don't have a forum association.

 

[Kevin]

Chris, thanks, that'll take care of exactly what I was looking for.

phpMyAdmin GUI was throwing an error because the inner select might return multiple rows so all I had to do was add a 'LIMIT 1' to the inner select.

SELECT *
FROM xf_thread_prefix AS tp
WHERE (
    SELECT prefix_id
    FROM xf_forum_prefix AS fp
    WHERE fp.prefix_id = tp.prefix_id
    LIMIT 1
) IS NULL

For anybody reading this wanting to do the same thing, you can then take the prefix_id value in the results and use a URL like https://{YourXFURL}/admin.php?thread-prefixes/prefix.{prefixID}/edit to edit the prefix directly