#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0

    Number of subquery levels in From statement


    Hallo all,

    This week I discovered the wonders of a subquery in the From statement. (thanks again Rudy!). Very usefull indeed.

    I am trying to make a query that gives me all the information about athlete names (FirstName, PreFix, LastName, Country) that are double (entered more than once).
    The table where to get this information has athletes that are deleted (flagged), but not removed. This gives me more information than needed, apart from that they keep interfering with my results. Therefore my first step is to remove these athletes from the list (step 1 in the code).
    Based on this “clean list” I count and select the athletes that occur more than once (only the basic fields). This is step 2.
    So far so good.

    When I try to combine the results from step 1 and 2 with an Inner Join (step 3) I get the message that (e.g.) CleanList.prsFirstName doesn’t exist.
    Is this because the results from Step 1 are not combinable with step 3?

    I have solved the issue by capturing step 1 in a view, but I would like to do it all in 1 query (if possible). (My current solution works).
    Can it be done?


    Code:
    /* Begin Step 3 – making the final report */
    
    SELECT GroupedList.prsFirstName, 
           GroupedList.prsPreFix, 
           GroupedList.prsLastName, 
           GroupedList.prsCountryID, 
           tblcountries.cntCountryCode, 
           tblcountries.cntNameISU, 
           GroupedList.Aantal
    
    /* Begin Step 2 – Determine which names are double  */
    FROM (
    SELECT CleanList.prsFirstName, 
          CleanList.prsPreFix, 
           	      CleanList.prsLastName, 
           	      CleanList.prsCountryID, 
           	      CleanList.prsSyncState, 
           	      COUNT(CleanList.prsLastName) AS Aantal
    
    /* Begin Step 1 – Selection of athletes that meet criteria */
    
    FROM (SELECT  tblpersons.prsID, 
       		 tblpersons.prsFirstName, 
         		 tblpersons.prsPreFix, 
        		 tblpersons.prsLastName, 
         		 tblpersons.prsCountryID, 
          		 tblpersons.prsGender, 
         		 tblpersons.prsBirthDate, 
         		 tblpersons.prsSyncState 
     FROM   tblpersons
     WHERE  tblpersons.prsSyncState = 0 ) AS CleanList
     /* End Step 1 */ 
     
                  GROUP BY CleanList.prsFirstName, CleanList.prsPreFix, CleanList.prsLastName,    
      CleanList.prsCountryID, CleanList.prsSyncState 
                  HAVING COUNT(CleanList.prsLastName) > 1) as GroupedList 
     
     /* End Step 2 */
     
    Further: inner join tblcountries
    Doesn’t regonise CleanList (which is on level 1 in the From statement)
    
    /* End Step 3 */
    Thanks
    Hein
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    if you are going to nest subqueries in the FROM clause, especially if you do it more than one level deep, it's ~imperative~ (in my opinion) that you indent them properly...
    Code:
    SELECT GroupedList.prsFirstName
         , GroupedList.prsPreFix
         , GroupedList.prsLastName
         , GroupedList.prsCountryID
         , tblcountries.cntCountryCode
         , tblcountries.cntNameISU
         , GroupedList.Aantal
      FROM ( SELECT CleanList.prsFirstName
                  , CleanList.prsPreFix
                  , CleanList.prsLastName
                  , CleanList.prsCountryID
                  , CleanList.prsSyncState
                  , COUNT(CleanList.prsLastName) AS Aantal
               FROM ( SELECT tblpersons.prsID
                           , tblpersons.prsFirstName
                           , tblpersons.prsPreFix
                           , tblpersons.prsLastName
                           , tblpersons.prsCountryID
                           , tblpersons.prsGender
                           , tblpersons.prsBirthDate
                           , tblpersons.prsSyncState
                        FROM tblpersons
                       WHERE tblpersons.prsSyncState = 0 ) AS CleanList
             GROUP 
                 BY CleanList.prsFirstName
                  , CleanList.prsPreFix
                  , CleanList.prsLastName
                  , CleanList.prsCountryID
                  , CleanList.prsSyncState
             HAVING COUNT(CleanList.prsLastName) > 1 ) as GroupedList
    this makes it ~far~ easier to see which portions of code exist at each level

    the reason you cannot join to Cleanlist in the outer level is because that table is not visible to the outer level -- only GroupedList is
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    5
    Rep Power
    0
    Hi Rudy

    Thanks again for your reply.
    So my assumption was right: the 1st group / level is no longer visible on "level 3".
    So my current solution remains, using a view to "clean up" the table, and work with these results.

    About your comment on lay-out: you are absolutely right. In fact my "adding spaces/blanks" was a vague attempt at that, except for the comma. As a rule I place it at the end of a line. But I guess that's a personal preference.

    Thanks
    hein

    PS
    Is your book available in Dutch?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Sport
    So my current solution remains, using a view to "clean up" the table, and work with these results.
    no, i don't think you need to do that

    just join the tblcountries table to GroupedList.prsCountryID



    dutch? sorry, i don't think so, but the english in there is really simple
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo