Remember that not all SQL dbs support nested sub queries - ie MySQL (pre v5.0).
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)
This is the cute bitVB: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
We set an alias up of the gchg table in our nested SELECTVB:WHERE.... AND gchg.effective = ( SELECT MAX(gchg_alias.effective) FROM gchg As gchg_alias WHERE gchg_alias.id = gchg.id)
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.
Last edited by Will Riley; May 25th, 2004 at 19:33. Reason: Edited to clean up code tags from XMB
Kind Regards, Will Riley
LinkedIn: Will Riley
If you needed to run more than one query against this you could simply build a temp table to hold the subquery data:
This would make no difference if you are just running the one query, but if you wanted to run more than one against the same subset it would be much quicker.VB:SELECT MAX(gchg_alias.effective) INTO #Temp FROM gchg As gchg_alias WHERE gchg_alias.id = gchg.id SELECT gchg.id, grup.desc_, gchg.effective, grup.type FROM gchg,grup,#temp WHERE grup.rowno = gchg.rowno_grupgchg_grup And grup.type = 'revenue' And grup.status = 'active' AND gchg.effective = #temp.gchg_alias.effective ORDER BY gchg.id ASC
There are currently 1 users browsing this thread. (0 members and 1 guests)