Here is an example of NESTING one SQL query within another
in order to return a subset of data that could not be done within normal SELECT parameters....
Here's the scenario I had,
I have two tables grup and gchg
grup contains the ID & Description Fields I want
gchg contains many records for each grup.ID - I want just the
LATEST record for each Grup.ID (specified by the latest effective date)
So, we need to find the maximum effective date in the gchg table for each ID.
The tables can be linked - 1 to many.
BTW - reason, there are 41,000 records in the gchg table... to import this into XL
would be suicidal, when I only need the 80 latest records for a simple VLOOKUP
OK, Here's the SQL (This is from SQL 2000 Query Analyser)
SELECT gchg.id, grup.desc_, gchg.effective, grup.type FROM gchg,grup WHERE grup.rowno = gchg.rowno_grupgchg_grup AND grup.type = 'revenue' AND grup.status = 'active' AND gchg.effective = ( SELECT MAX(gchg_alias.effective) FROM gchg AS gchg_alias WHERE gchg_alias.id = gchg.id) ORDER BY gchg.id ASC
This is the cute bit
WHERE.... AND gchg.effective = ( SELECT MAX(gchg_alias.effective) FROM gchg AS gchg_alias WHERE gchg_alias.id = gchg.id)
We set an alias up of the gchg table in our nested SELECT
statement and link it to gchg using the ID field of both
FROM gchg AS gchg_alias 'names the alias table
WHERE gchg_alias.id = gchg.id ' links them together
SELECT MAX(gchg_alias.effective) 'gets the Max of each groupIDs effective date
Using a further select query as part of the WHERE clause
saves us having to do multiple separate queries to pare down are data
to manageable subsets.....
In my case, It meant a very easy way of importing 80 records as opposed to 41,000
Hope the structure helps someone else.