MySQL using subqueries instead of JOIN

Question

I need to show the names of all artists which have at least one female member each.

The Members table looks as follows:

Members
-------
MemberID, Firstname, Lastname, Address, Gender

The Artists table looks as follows:

Artists
-------
ArtistID, Artistname, City

The tables are related using the xRefArtistsMembers table, which is as follows:

xRefArtistsMembers
------------------
MemberID, ArtistID

I have formulated the following subquery:

select
  ar.artistname from artists ar
where
  ar.artistid in
  (
    (
      select
        x.artistid
      from
        xrefartistsmembers x
    )
    in
    (
      select
        m.memberid
      from
        members m
    )
  );

which does not compile. Again, my question is: I need to show the names of all artists which have at least one female member each. With using only subqueries.

Answer 1

Try

SELECT *
  FROM artists a
 WHERE EXISTS
(
  SELECT *
    FROM xRefArtistsMembers x
   WHERE artistid = a.artistid
     AND EXISTS
  (
    SELECT * 
      FROM members 
     WHERE memberid = x.memberid
       AND gender = 'female'
  )
)

Here is SQLFiddle demo