Announcement

Collapse
No announcement yet.

SQL: Nested SELECT statements

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL: Nested SELECT statements



    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)

    Code:
    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

    Code:
    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.


    Will
    Last edited by Will Riley; May 25th, 2004, 19:33. Reason: Edited to clean up code tags from XMB
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  • #2
    Remember that not all SQL dbs support nested sub queries - ie MySQL (pre v5.0).

    Comment


    • #3


      If you needed to run more than one query against this you could simply build a temp table to hold the subquery data:
      Code:
      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
      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.

      Comment

      Working...
      X