How do I construct this select (with sub-select column), please?

[andrew cooke]

Hi,

I thought I had done this before, but now I can't work out what I did.
 I would like to construct this query, given that the appropriate
tables etc are defined:

select ARTISTS.id, ARTISTS.value
  from ARTISTS
  left join (select ARTIST_CANONICAL.artist
               from ARTIST_CANONICAL
               join LFM_ARTIST_TAGS
                 on ARTIST_CANONICAL.canonical = LFM_ARTIST_TAGS.source) as X
    on ARTISTS.id = X.artist
 where X.artist is null;

In case it helps, the logic is that I want the rows from ARTISTS that
do not have any link, via the association table ARTIST_CANONICAL, with
LFM_ARTIST_TAGS.

It's all "trivial" except for "ARTISTS.id = X.artist" - I have a
DBCommand that represents X, but it has no attribute for the column.
Somehow I need to convert the command to something that is table-like
(I think).

Thanks,
Andrew