To make a long story short, I have a custom post type called artworks with these meta fields:

title
catalog_no
artist_key

I have a second custom post type called artists with these meta fields:

artist_key
artist_firstname
artist_lastname

Obviously, the artist_key field is used to relate the artist to their works. It contains alphanumeric strings such as "anvcih5-8745".

On category archives pages such as , I want to sort the results by artist_lastname.

In SQL I believe I would achieve this by doing an INNER JOIN, but I'm struggling to understand how to achieve the same thing in Wordpress.

I've figured out that I can add a posts_join filter that will allow me to join to another table. I have written the following hook and confirmed that it's firing on the correct page:

function archives_artwork_join($join)
{
    if ( !is_admin() ) {

        $qo = get_queried_object();
        if ($qo->name == 'sculpture') {
          // modify $join here
        }

        return $join;
    }
}
add_filter( 'posts_join', 'archives_artwork_join' );

But, I don't know how to write the JOIN statement. And I don't even know whether I am on the right track with this approach.

Am I on the right track here, or is there a better approach I could take to sort the artworks category archives by artist last name?