Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: SQL: Nested SELECT statements

  1. #1
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678
    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)

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

    VB:
    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 at 19:33. Reason: Edited to clean up code tags from XMB
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  2. #2
    Join Date
    25th May 2004
    Location
    London
    Posts
    447
    Remember that not all SQL dbs support nested sub queries - ie MySQL (pre v5.0).

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th August 2004
    Posts
    2
    If you needed to run more than one query against this you could simply build a temp table to hold the subquery data:
    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 
    
    
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Nested IF Statements
    By Rmcnaught in forum EXCEL HELP
    Replies: 5
    Last Post: July 13th, 2006, 06:03
  2. Alternative to nested IF statements
    By tja26 in forum EXCEL HELP
    Replies: 9
    Last Post: February 16th, 2006, 01:31
  3. Nested if Statements
    By MeKaren in forum EXCEL HELP
    Replies: 4
    Last Post: February 10th, 2006, 07:21
  4. nested if statements using Lookup
    By aceensor in forum EXCEL HELP
    Replies: 12
    Last Post: December 10th, 2005, 02:29
  5. Cannot have more than 7 nested IF statements
    By majestique in forum EXCEL HELP
    Replies: 7
    Last Post: October 18th, 2005, 17:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno