www.webdeveloper.com
Results 1 to 3 of 3

Thread: Need help joining tables

  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    700

    Need help joining tables

    Hi All,

    How easily one's brain forgets. It's been quite a while since I used JOIN in mySQL and I'm struggling to remember...

    I have two tables.

    The first, 'attendees', lists members who attended a particular event as follows;
    date = the event date
    mrn = the member record number

    It might look like

    date..mrn
    2012-08-24..1504
    2012-08-24..1621
    2012-08-21..3455
    2012-08-24..8891
    2012-08-24..1110
    2012-09-24..1631
    2012-09-24..4455
    2012-08-24..6329

    The second, 'friends', lists members who are friends of an individual member as follows
    mrn = the member's record number
    frn = the record number of a member who is a friend of the member in mrn

    it might look like

    mrn..frn
    1515..1631
    1681..6695
    1515..7841
    1515..6200

    I want to look at all members who attended an event on, say, 2012-08-24, and determine if any of them were friends of member 1515.

    If no attendees were friends, then the query should return 0 rows.
    If at least one attendee is a friend of 1515, then the query should return more than 0 rows.

    For this query, I don't actually need the data... I just need a yes/no answer on whether friends of 1515 attended, so I figured I'd get that from the number of rows returned. In the case above, there would be 1 row.

    I'm not even coming close to figuring out how to construct that query so any help will be greatly appreciated.

    Cheers
    CTB
    Oh Lord, please help me be the person my dog thinks I am.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,227
    Assuming no silly mistakes on my part:
    Code:
    SELECT COUNT(*) AS num_friends_attending
    FROM attendees
    INNER JOIN friends ON attendees.mrn = friends.frn
    WHERE friends.mrn = 1515 AND attendees.date='2012-08-24'
    Off hand, I don't know if that would be more or less efficient than using a sub-query, e.g.:
    Code:
    SELECT COUNT(*) AS num_friends_attending
    FROM attendees
    WHERE date='2012-08-24' AND mrn IN(
      SELECT frn FROM friends WHERE mrn=1515
    )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    700
    Once again, you're a lifesaver!
    That works perfectly.
    Thanks you.
    Oh Lord, please help me be the person my dog thinks I am.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles