SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Many-to-many!

  1. #1
    SitePoint Member
    Join Date
    Nov 2000
    Location
    Cheshire, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Many-to-many!

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2000
    Location
    Cheshire, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy - you're a star


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •