0

I have 3 tables 

members (id, name, email)
members_kids (id, member_id, kids_name, kids_age)
member_kids_activity_preferences (id, kid_id, activity_id)

I have an array of activities (4, 10, 12, 14)

What I need to do is select distinct email addresses from members WHOSE kids have a preference for 4,10,12,14 from the member_kids_activity_preferences table. How do i do it using 1 query ?

Jason

Mouna Cheikhna
  • 38,870
  • 10
  • 48
  • 69
Jason
  • 1

3 Answers3

2

Quick shot:

SELECT DISTINCT email FROM members m
    JOIN members_kids k ON m.id = k.member_id
    JOIN member_kids_activity_preferences p ON k.id = p.kid_id
  WHERE p.activity_id IN (4,10,12,14)
glglgl
  • 89,107
  • 13
  • 149
  • 217
2
SELECT m.name,m.email
FROM members m
   JOIN members_kids k
      ON m.id = k.member_id
   JOIN member_kids_activity_preferences ap
      ON k.id = ap.kid_id
WHERE ap.id IN (4, 10, 12, 14)
GROUP BY m.name,m.email
Dalen
  • 8,856
  • 4
  • 47
  • 52
  • i've edited my answer to use GROUP BY istead of DINSTINC just to propose something different from Mchl and glglgl as we posted almost simultaneous – Dalen Aug 09 '11 at 19:18
  • here is an interesting question about DISTINCT vs GROUP BY: http://stackoverflow.com/questions/426723/sql-group-by-versus-distinct – Dalen Aug 09 '11 at 19:24
0
SELECT DISTINCT
  m.email
FROM
  members AS m
INNER JOIN
  members_kids AS mk
ON
  m.id = mk.member_id
INNER JOIN
  member_kids_activity_preferences AS mkap
ON
  mk.id = mkap.kid_id
WHERE
  mkap.activity_id IN (4,10,12,14)
Mchl
  • 61,444
  • 9
  • 118
  • 120