Complex SQL Query - nested query

Question

Here is the job_form table...

job_num  | name | address |
---------+------+---------+
 1       | Tom  | Smith   |
 2       | John | Doe     |
 3       | Max  | Smith   |

Here is the individual_job table...

job_num  | date       | description   |
---------+------+---------------------+
 1       | 23-01-2012 | Eat food      |
 1       | 24-01-2012 | Do dishes     |
 1       | 25-01-2012 | Sweep floor   |
 ...     | ...        | ...           |
 2       | 19-05-2013 | Play games    |
 2       | 23-05-2013 | Do code       |
 2       | 27-05-2013 | Sleep         |
 ...     | ...        | ...           |
 3       | 23-05-2013 | Eat food      |
 3       | 24-05-2013 | Do dishes     |
 3       | 25-05-2013 | Sweep floor   |
 ...     | ...        | ...           |

I would like to create a query that pulls out a single row for each job_form which includes the date of the first job to be completed, the date of the last job to be completed as well as the total number of jobs listed on the form. The query needs to display only job forms which have jobs which need to be completed in the future.

Example is:

job_num  |  first_job_date  |  last_job_date  |  count_of_jobs  |  name
---------+------------------+-----------------+-----------------+-------------
2        |  19-05-2013      |  27-05-2013     |  3              |  John
3        |  23-05-2013      |  25-05-2013     |  3              |  Max

I haven't done SQL for a few years, and this one has me completely stumped. I know I have to do a nested query, but can't work out the order...

Any help much appreciated.

Updated to include name column in result (forgot about this, sorry)

Gordon Linoff · Accepted Answer · 2013-05-09 14:01:40Z

3

This is a simple aggregation query:

select ij.job_num,
       min(ij.date) as first_job_date,
       max(ij.date) as last_job_date, count(*) as count_of_jobs
from individual_job ij
group by ij.job_num

For future jobs, you need a date comparison, something like the following (depending on the database):

where date >= sysdate

or

where date >= now()

or

where date >= getdate()

The where clause goes after the from clause.

answered May 9, 2013 at 14:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StuartLC · Accepted Answer · 2013-05-09 14:26:00Z

0

Edit As per Gordon, there is no need for a join if you don't require any of the job_form specific fields. However, if you do (e.g. name or address), then:

SELECT jf.job_id,
       jf.name, 
       MIN(ij.date) AS first_job_date, 
       MAX(ij.date) as last_job_date, 
       COUNT(*) as count_of_jobs
FROM job_form jf
  INNER JOIN individual_job ij
    ON jf.job_num = ij.job_num
GROUP BY jf.job_id, jf.name -- i.e. non-aggregated select fields here

edited May 9, 2013 at 14:26

answered May 9, 2013 at 14:02

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Collectives™ on Stack Overflow

Complex SQL Query - nested query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related