== Trouble writing the correct query == 1

[Lynux]

I have a table of "activities" and a table of "user_activities".... once a user completes an activity in the "activities" table and logs that in on our web site it shows up as a completed task... here is the query that is used:
==========================>

Select fname, lname, user_activities.activities_ID, activities.description
from users, user_activities, activities where users.pin = 5099 AND user_activities.PIN = 5099 AND
user_activities.activities_ID = activities.ID

====================================================
The big question is... How do I query the table "activities" using the table "user_activities" to show all of the activities that the user has not completed yet? I tried the obviouse Query:
=======================>

Select fname, lname, user_activities.activities_ID, activities.description
from users, user_activities, activities where users.pin = 5099 AND user_activities.PIN = 5099 AND
user_activities.activities_ID <> activities.ID

======================================================

That was the furthest thing from the output I was looking for........... Anyone have any suggestions?
If you can answer this one you got my vote!

Thanks a ton in advance! =================================
Imagination is more important than knowledge.
(A.E.)

 

[tlbroadbent]

See answer in thread220-190549. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[rac2]

Is activities a list of possible activities?
And is user_activities a list of activities completed?

If so, then this query is a list of activities completed by user 5099.

SELECT pin, activities_ID FROM user_activities WHERE pin = 5099

The next query is a list of activities that should be completed by user 5099. Those that are not completed will have NULL for the oneUser.pin.

SELECT  activities.description, oneUser.pin
  FROM activities
  LEFT JOIN (
             SELECT pin, activities_ID FROM user_activities WHERE pin = 5099
             ) oneUser ON activities.ID = oneUser.activities_ID

Thus a list of incomplete activities is -

SELECT  activities.description, oneUser.pin
  FROM activities
  LEFT JOIN (
             SELECT pin, activities_ID FROM user_activities WHERE pin = 5099
             ) oneUser ON activities.ID = oneUser.activities_ID

WHERE oneUser.pin IS NULL

But oneUser.pin is null so leave it out of the column list. Put in the activity ID and get the name of user 5099 -

SELECT fname, lname, activities.activities_ID, activities.description
  FROM activities, users
  LEFT JOIN (
             SELECT pin, activities_ID FROM user_activities WHERE pin = 5099
             ) oneUser ON activities.ID = oneUser.activities_ID

WHERE oneUser.pin IS NULL
   
   AND users.pin = 5099

Anyway it seems plausible. Please let me know whether this works.

Richard

 

[Lynux]

This is perfect!.... thanks Richard
=================================
Imagination is more important than knowledge.
(A.E.)