5 Replies - 2850 Views - Last Post: 02 September 2012 - 04:52 PM

Help displaying a query properly.

Posted 30 August 2012 - 06:03 PM

Okay, so I have a table of each workers health and safety records. Each worker has completed two different health and safety applications, one in each year. Below is an example of the health and safety records ordered by worker_id. In a seperate table, I have a list of dates which all of the workers have accessed the site. What I am trying to is to create a query which will see if any employees had accessed the site while their expiry date had expired.

Its doing what its supposed to do by showing accesses which are beyond the expiry date but it is also showing results I do not want to see because each user has two expiry dates. I need to select the maximum expiry date for each user.

Ive tried adding clauses such as hsExpiryDate = '2011%' AND accessDate = '2011%' so that it only matches accesses with expirys of that year but have had no luck. Is anyone able to help me fix this problem?

Re: Help displaying a query properly.

Posted 30 August 2012 - 07:07 PM

thanks fromTheSprawl, I wasn't aware you could do that with MAX. How would I go about doing this? Would I need to use a sub-query to get the highest date for each worker and then use the sub query with my original query?

Re: Help displaying a query properly.

Posted 30 August 2012 - 07:37 PM

I'm not really good with SQL queries, but yeah, the general idea is to get the larger of them.

In a simple database where theoretically we have a table with columns workerID and expiryDate and values can double with this example:workerID expiryDate
1 November 10, 2012
1 December 10, 2012
2 September 10, 2012
2 October 10, 2012
3 April 10, 2012
3 July 10, 2012

I'd do this simple query:

SELECT workerID, MAX(expiryDate) group by workerID;

In fact, you can test it now just by using wID, hsExpiryDate and yourTableName instead. Now to get the query the way you wanted with the extra columns, try it the way you said you'd do it. Alas, my skills on SQL are lacking.