asked on

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.

Hamed Nasr

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cornelia Yoder

ASKER

That got me exactly where I needed, thanks so much Paul!