Number of subquery levels in From statement

Dev Shed Forums Sponsor:

January 24th, 2013, 01:35 PM

Sport

Registered User

Join Date: Jan 2013

Posts: 5

Time spent in forums: 3 h 19 m
Reputation 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

January 24th, 2013, 02:08 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 18 sec
Reputation Power: 4140

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

January 24th, 2013, 02:16 PM

Sport

Registered User

Join Date: Jan 2013

Posts: 5

Time spent in forums: 3 h 19 m
Reputation 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?