MySQL query needed

I have tables as follows:

MembersTable
---------
  MemberNumber
  LastName

Example Data:
  1 Smith
  2 Jones
  3 Brown


CompletedAwardsTable
---------
  MemberNumber
  AwardWon
  AwardDate

Example Data:
  1  A  2012-01-01
  2  A  2012-02-02
  1  B  2012-03-03



AwardItemsTable
---------
  MemberNumber
  CountTowardAward
  ItemNumber
  ItemDate

Example Data:
  1  A  item1A  2011-01-01
  1  A  item2A  2012-01-01
  1  B  item1B  2011-02-02
  1  B  item2B  2012-03-03
  2  A  item1A  2011-01-01
  2  A  item2A  2012-02-02
  2  B  item1B  2011-02-02
  1  C  item1C  2012-04-04



I need a query which will, given an Award, give me back the list of members who have completed an item toward that award (AwardItemsTable), but not yet completed the award (CompletedAwardsTable).  I also need the latest date of a completed item for that member for that award.  And I want the result list to be alphabetical by LastName.

So if I ask for Award A, I should get no members, since everyone who has completed an A item has also completed the A award.

If I ask for Award B, I should get member 2 (Jones) and latest date 2011-02-02, since Jones has completed an item for B, but not completed the B award.  I should not get member 1 (Smith), since Smith has completed the B award.  I should not get member 3 (Brown), since Brown has not completed any items for the B award.

If I ask for Award C, I should get member 1 (Smith) and latest date 2012-04-04, since Smith has completed an item for award C, but not completed the C award.  I should not get member 2 (Jones) nor member 3 (Brown), since neither has completed any item for award C.


I'm pretty sure this needs a JOIN, and I suck at JOINs, so would appreciate the help.
LVL 27
Cornelia YoderArtistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Try:

SELECT m.MemberNumber, m.LastName, c.MemberNumber, c.AwardWon, c.AwardDate,
       a.MemberNumber, a.CountTowardAward, a.ItemNumber, a.ItemDate
FROM (MembersTable As m
     INNER JOIN AwardItemsTable As a ON c.MemberNumber=a.MemberNumber)
     LEFT JOIN CompletedAwardsTable As c ON m.MemberNumber=c.MemberNumber
                 AND c.AwardWon = a.CountTowardAward
WHERE m.MemberNumber IS NULL;

Open in new window

0
PortletPaulfreelancerCommented:
If you want a just one row per member returned use a group by
SELECT
        m.MemberNumber
      , m.LastName
      , a.CountTowardAward
      , max(a.ItemDate) as ItemDate
FROM MembersTable AS m
INNER JOIN AwardItemsTable AS a
        ON m.MemberNumber = a.MemberNumber
LEFT JOIN CompletedAwardsTable AS c
        ON m.MemberNumber = c.MemberNumber
        AND c.AwardWon = a.CountTowardAward
WHERE c.MemberNumber IS NULL
AND a.CountTowardAward = 'B' /* change to suit */
GROUP BY
        m.MemberNumber
      , m.LastName
      , a.CountTowardAward
ORDER BY
        m.LastName
;

Open in new window

please note these:

INNER JOIN AwardItemsTable AS a
        ON m.MemberNumber = a.MemberNumber /*  m.   not c. */
..
WHERE c.MemberNumber IS NULL  /* c.   not m. */
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cornelia YoderArtistAuthor Commented:
That got me exactly where I needed, thanks so much Paul!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.