T
Timothy V
Hi everyone, i hope someone can help me construct this query. To make it
easy, I will provide a simplified example of my problem and relationships
will exist between fields that have the same names. Three tables exist:
Programs(programID) - pk is programID
Jobs(jobID,dueDate) - pk is jobID
ProgrammedJobs(programID,jobID) - pk is programID and jobID
Ok, so what I want to do is create a query that will list the programIDs
without duplicates... that is, similar to SELECT DISTINCT and GROUP BY. So,
not this:
programID
2000
1000
2000
1000
1000
1000
But this:
programID
1000
2000
However, there is a twist... Since one programID will have many jobIDs, I
want to sort the query of programIDs that have jobIDs with earlier dates. To
help you understand, here is some data.
Program (1000) has Jobs (100,1/21/2007), (101,1/31/2007), (102,1/31/2007)
associated with it.
Program (2000) has Jobs (103,1/19/2007), (104,1/31/2007), (105,1/28/2007)
associated with it.
Basically, the query I want will have the data (in order):
programID
2000
1000
Where 2000 has a job that is more due before any that are within 1000.
Can anyone help me?
Thank you very very very much in advance,
Tim.
PS. here is my current query that can sort the data, but not group the
program numbers:
SELECT Programs.programID
FROM Programs INNER JOIN (Jobs INNER JOIN ProgrammedJobs ON (Jobs.jobAsn =
ProgrammedJobs.jobAsn) AND (Jobs.jobID = ProgrammedJobs.jobID)) ON
Programs.programID = ProgrammedJobs.programID
ORDER BY Jobs.dueDate;
easy, I will provide a simplified example of my problem and relationships
will exist between fields that have the same names. Three tables exist:
Programs(programID) - pk is programID
Jobs(jobID,dueDate) - pk is jobID
ProgrammedJobs(programID,jobID) - pk is programID and jobID
Ok, so what I want to do is create a query that will list the programIDs
without duplicates... that is, similar to SELECT DISTINCT and GROUP BY. So,
not this:
programID
2000
1000
2000
1000
1000
1000
But this:
programID
1000
2000
However, there is a twist... Since one programID will have many jobIDs, I
want to sort the query of programIDs that have jobIDs with earlier dates. To
help you understand, here is some data.
Program (1000) has Jobs (100,1/21/2007), (101,1/31/2007), (102,1/31/2007)
associated with it.
Program (2000) has Jobs (103,1/19/2007), (104,1/31/2007), (105,1/28/2007)
associated with it.
Basically, the query I want will have the data (in order):
programID
2000
1000
Where 2000 has a job that is more due before any that are within 1000.
Can anyone help me?
Thank you very very very much in advance,
Tim.
PS. here is my current query that can sort the data, but not group the
program numbers:
SELECT Programs.programID
FROM Programs INNER JOIN (Jobs INNER JOIN ProgrammedJobs ON (Jobs.jobAsn =
ProgrammedJobs.jobAsn) AND (Jobs.jobID = ProgrammedJobs.jobID)) ON
Programs.programID = ProgrammedJobs.programID
ORDER BY Jobs.dueDate;