Many-to-many!

[dom]

Hi All!

I was hoping someone could help me out with this problem.

I have two tables with a many-to-many relation in the following format...

MEMBERS(MemberID, Username):
1   Member1
2   Member2
3   Member3
4   Member4

NEWSLETTERS (NewsletterID, Title):
1   Newsletter1
2   Newsletter2
3   Newsletter3
4   Newsletter4

SUBSCRIPTIONS (MemberID, NewsletterID):
1   1
1   3
2   1
2   2
2   3
3   2
3   4
4   1
4   2

So basically the SUBSCRIPTIONS table stores the "who is subscribed to what" data.

Now, for a given member I would like to return ALL newsletters togther with a TRUE/FALSE variable depending on whether that member has subscribed to each one.

For example, if I was Member3 the following rows would be returned if the data above applied...

NEWSLETTER    SUBSCRIBED
Newsletter1         No
Newsletter2         Yes
Newsletter3         No
Newsletter4         Yes

I've tried all sorts of SELECT queries with joins - but none seem to product this result. Am I missing something here?!

BTW, I am using MySQL and PHP.

Thanks a lot,
Dom

[r937]

select Username
     , Title
     , CASE WHEN Subscriptions.NewsletterID is null 
          THEN "No" ELSE "Yes" END as Subscribed
  from Members
cross
  join Newsletters
left outer
  join Subscriptions
    on Members.MemberID = Subscriptions.MemberID
   and Newsletters.NewsletterID = Subscriptions.NewsletterID

caution: untested

[dom]

Thanks rudy - you're a star