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.

You don't have a column that indicates the gender of member. — Dai
– Dai, Commented Nov 22, 2013 at 4:12
one female member each ? how will you find .. Im not seeing any Gender column — backtrack
– backtrack, Commented Nov 22, 2013 at 4:13
Let me apologize, I accidently forgot to include that crucial peice of record data. — user2948897
– user2948897, Commented Nov 22, 2013 at 4:14
What does your title mean? Do you have to use sub-queries instead of JOIN? — PM 77-1
– PM 77-1, Commented Nov 22, 2013 at 4:15

peterm · Accepted Answer · 2013-11-22 04:20:37Z

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

answered Nov 22, 2013 at 4:20

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2948897 Over a year ago

Thanks for your response. For some reason the following query: select * from artists ar where artistname in (select gender from members where members.gender = 'F'); For some reason produces an EMPTY SET>

peterm Over a year ago

You're quite welcome. ;) How can the query you mentioned in the comment above produce anything when you compare artistname in outer select to gender in inner subquery..?

user2948897 Over a year ago

Very correct. Ok Thanks I think I understand more simply by the 'EXISTS' statement

Collectives™ on Stack Overflow

MySQL using subqueries instead of JOIN

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related