Need help converting correlated subquery to a join...

Actually that's those are correlated subqueries; subqueries, yes, but not correlated.

The queries will be executed from the bottom up.  That is, this will be done first:

( SELECT CompactDiscID FROM CompactDiscs WHERE CDTitle = 'Past Light' )

Then this:

( SELECT ArtistID FROM ArtistCDs WHERE CompactDiscID IN ...)

and finally:

SELECT ArtistName FROM Artists
WHERE ArtistID IN ...

The join statement would be:

SELECT a.ArtistName
FROM Artists a inner join ArtistCDs ac on a.artistid = ac.artistid
inner join CompactDiscs cd on cd.compactdisid = ac.compactdiscid and CDTitle = 'Past Light'

In a correlated query, the subquery is first evaluated and the result is substituted in the outer query. In this case (your example) it happens twice because there are two sub queries.

The join version would be:

SELECT ArtistName
FROM Artists
INNER JOIN ArtistCDs ON Artists.ArtistID = ArtistCDs.ArtistID
INNER JOIN CompactDiscs ON ArtistCDs.CompactDiscID = CompactDiscs.CompactDiscID AND CompactDiscs.CDTitle = 'Past Light' ;

Not necessarily.  Correlated means that a subquery will run once for every value in an outer query.  If each query can run just once, then they're not correlated.

For example, this query *is* correlated:

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

Note that the subquery has a reference to a column in the outer query -- "authors.au_id".  This is a trademark of a correlated subquery -- within the body of the subquery itself it references a column from an outer query.

>> Or if several artists collaborated on the same CD, wich is strongly suggested by the table design ... and by the usage of "IN" rather than "=" in the query initially posted by jamesreddy.

The IN would be needed to handle multiple different artists, not necessarily the same one.  For example, 10 different people could have CDs named 'Past Light'.  IN would be necessary even with no dups.

>> Don't you think the ArtistCDs table suggests a many-to-many relationship.
If one CD has one artist, this table is useless ... <<

True, you're right there.  DISTINCT would still only be needed if the same artist worked on more than one CD with the specific title given; that's possible, albeit unlikely.

You could also write it as  

SELECT ArtistName FROM Artists as A
WHERE Exists
( SELECT AC.ArtistID
    FROM ArtistCDs as AC
   WHERE Ac.Artistid = A.ArtistID
        And Exists  ( SELECT C.CompactDiscID
                             FROM CompactDiscs as C
                           WHERE CDTitle = 'Past Light'
                                and c.compactdiscid=ac.compactdiscid
                          )
 )


which does use correlated subqueries



the INNER join gives you the INTERSECTION of the Result sets
the Full Outer Join  show all data around the intersection
the Left/Right Joins Give you Everything (on the left or right plus the Insection data)
the Cross product join shows all data from the join tables combined with each other..

hth