Solved

mssql subqueries, losing table references

Posted on 2008-10-14
16
562 Views
Last Modified: 2012-05-05
Im working off of an SQL 2000 warehouse and trying to page results for a subquery. MySQL makes it easy with "select * from [table] LIMIT 1, 3" (3 records starting from the 1st record).

SQL 2000 requires trickery with nested subqueries

SELECT * FROM (
        SELECT TOP x * FROM (
               SELECT TOP y fields
               FROM table
               WHERE conditions
               ORDER BY table.field  ASC) as foo
        ORDER by field DESC) as bar
ORDER by field ASC
x is the number of rows you want returned and y is x+offset.


With that in mind& if Im running one query that needs a subquery to return paged results (I want the 1st contact, or the 3rd contact, etc), that one subquery has 2 subqueries under it. My problem is that by the time it gets to the innermost subquery, it seems to have lost the reference to one of the tables from the parent query. Here is what Im doing, in a simplified manner to show the issue.

USE [GILVSAM]

SELECT
      dbo.SBR.SBRSTDT AS [Student ID] ,
      dbo.SBR.SBRLAST AS [Last Name] ,
      dbo.SBR.SBRFIRST AS [First Name] ,
      (
        SELECT TOP 1
            sciname
        FROM
            (
              SELECT TOP 1
                  *
              FROM
                  (
                    SELECT TOP 1
                        *
                    FROM
                        sci
                    WHERE
                        scipu = 'Y'
                       
                        --at this point, it appears to have lost the reference to the table
                        --I get 'The column prefix 'dbo.SBR' does not match with a table name or alias name used in the query.'
                        --I've tried all sorts of workarounds but my Microsoft SQL Server experience doesn't encompass this.
                        AND ( SCISTDT = dbo.SBR.SBRSTDT )
                    ORDER BY
                        scics DESC ,
                        sciname ASC
                  ) AS foo
              ORDER BY
                  scics ASC ,
                  sciname DESC
            ) AS bar
        ORDER BY
            scics DESC ,
            sciname ASC
      ) AS [Contact 1 Name]
FROM
      dbo.SBR
      LEFT OUTER JOIN dbo.SCI AS SCI_1
            ON dbo.SBR.SBRSTDT = SCI_1.SCISTDT
WHERE
      ( dbo.SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
      AND ( dbo.SBR.SBRCSCHL = '0032' )
ORDER BY
      dbo.SBR.SBREXNBR ,
      dbo.SBR.SBRSTDT


I have attached the full query.

How to I "pass" the table reference down?


Basically the master query/report has columns for names of contact #1, contact #2, etc.. however each of these contacts are a separate record from another table

contact #1 name, phone, address, etc
contact #2 name, phone, address, etc

so I need to 'page' the results so the report looks like this

contact 1 name, contact 1 phone, contact 2 name, contact 2 phone, etc

Report.sql.txt
0
Comment
Question by:MMDeveloper
  • 10
  • 6
16 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22717945
Wow, 1200 lines... Was going to simply use Table Alias or Table name to clarify and seperately identify each data source...  Even though the column names are uniquely identified with the table as a prefix in the column name itself, you do need to help it out when there is more than one table of the same name...

Using the shortened query above by way of example...

and interestingly, the query is not using anything from the dbo.sci left outer join linked in the main "From"
SELECT
      SBR.SBRSTDT AS [Student ID] ,
      SBR.SBRLAST AS [Last Name] ,
      SBR.SBRFIRST AS [First Name] ,
      ( SELECT TOP 1 bar.sciname 
        FROM ( SELECT TOP 1 * 
               FROM ( SELECT TOP 1 *
                      FROM dbo.sci s
                      WHERE s.scipu = 'Y' AND s.SCISTDT = SBR.SBRSTDT
                      ORDER BY s.scics DESC , s.sciname ASC ) AS foo
               ORDER BY foo.scics ASC ,foo.sciname DESC ) AS bar
        ORDER BY bar.scics DESC , bar.sciname ASC ) AS [Contact 1 Name]
 
FROM  dbo.SBR SBR
LEFT OUTER JOIN dbo.SCI SCI ON SBR.SBRSTDT = SCI.SCISTDT              -- this is not used...
 
WHERE ( SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( SBR.SBRCSCHL = '0032' )
 
ORDER BY SBR.SBREXNBR , SBR.SBRSTDT

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22717960
oh, and in that sample query, there is no point nesting that deep - nor any point using an order by when the data source is going to return a single row anyway (e.g. ok within the foo subquery, but no point from the foo subquery, nor the bar subquery). But figured it is an example anyway, so maybe a moot point mentioning it.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22718208
Had a good look at that query, seems to be a better way, so long as there is a "uniqueness" that can be checked for contacts to make sure it is in sequence. Seems to be SCISTDT and SCICS, so, using that as a basis... Have a look at :




SELECT
      SBR.SBRSTDT AS [Student ID] ,
      SBR.SBRLAST AS [Last Name] ,
      SBR.SBRFIRST AS [First Name],
      SCI_1.SCINAME AS [Contact 1 Name],
      SCI_2.SCINAME AS [Contact 2 Name],
      SCI_3.SCINAME AS [Contact 3 Name],
      SCI_4.SCINAME AS [Contact 4 Name]
 
FROM  dbo.SBR
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT AND SCI_1.SEQ = 0
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_2 ON SBR.SBRSTDT = SCI_2.SCISTDT AND SCI_2.SEQ = 1
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_3 ON SBR.SBRSTDT = SCI_3.SCISTDT AND SCI_3.SEQ = 2
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_4 ON SBR.SBRSTDT = SCI_4.SCISTDT AND SCI_4.SEQ = 3
 
WHERE ( SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( SBR.SBRCSCHL = '0032' )
 
ORDER BY SBR.SBREXNBR , SBR.SBRSTDT

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22718225
Or, if lots and lots of contacts, then might like to try:

SELECT
      SBR.SBRSTDT AS [Student ID] ,
      SBR.SBRLAST AS [Last Name] ,
      SBR.SBRFIRST AS [First Name],
      MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME else '' END) AS [Contact 1 Name],
      MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME else '' END) AS [Contact 2 Name],
      MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME else '' END) AS [Contact 3 Name],
      MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME else '' END) AS [Contact 4 Name]
 
FROM  dbo.SBR
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT
GROUP BY SBR.SBRSTDT, SBR.SBRLAST, SBR.SBRFIRST 

Open in new window

0
 
LVL 15

Accepted Solution

by:
MMDeveloper earned 0 total points
ID: 22743906
I still kept getting column/alias errors. I ended up fixing it myself by populating temporary tables and selecting from them.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22746243
Really ? Still getting column / alias errors !? Can you please do a print screen / paste the error from the last query I posted - professional curiosity, and would really, really like to find out why...
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22746564
I'll have to wait until monday since that's at work. I do remember the error saying something about no column named 'seq' exists (like it didn't 'take' your "AS seq" alias).
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22746572
Wonder if it is being fussy about case sensitivity ? If you wouldn't mind checking on Monday, would like to find out... Thanks for your reply.
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22757416
the query without your last post worked (minus contact information) however plugging in your entire post gives this error

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SBR.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

if I leave in your left join, and take out the 4 max() lines (that display the column information), the error goes away and the query runs (but obviously without the contact information).
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22757587
Thanks for getting back... That is very curious considering SBR.SBREXNBR is not in that query... Curiouser and curiouser! cried Alice...
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22760660
well when it was plugged into the overall query that error occurs
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22760698
Does it work independantly of the rest ? Wanna show the new "entire" query, or are you happy with your workaround ?
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22766401
independently it works. I'm happy with my workaround (I forced the IT guys to upgrade that box to a SQL 2005 instance so I could use temporary tables with ROW_NUMBER(). I'll try to re-make the query to what it was when I tried to integrate your solution in it.

the associated error I get is:


Msg 8120, Level 16, State 1, Line 2
Column 'M.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


however the column SBREXNBR does indeed exist in the SBR (M) table.
USE [GILVSAM]
   SELECT TOP 100 PERCENT
            M.SBREXNBR AS [State ID] ,
            M.SBRSTDT AS [Student ID] ,
            M.SBRLAST AS [Last Name] ,
            M.SBRFIRST AS [First Name] ,
            M.SBRMIDDLE AS [Middle Initial] ,
            M.SBRSEX AS Gender ,
            M.SBRBIRTH AS DOB ,
            '(' + M.SBRAC + ') ' + M.SBREX + ' - ' + M.SBRNO AS [Phone Number] ,
            M.SBRRNBR + ' ' + M.SBRRDIR + ' ' + M.SBRRSTR + ' ' + M.SBRRTYPE
            + ' ' + M.SBRRAPT AS [Street Address] ,
            M.SBRRCITY AS City ,
            M.SBRRST AS State ,
            M.SBRRZIP5 AS Zipcode ,
            (
              SELECT
                  CASE WHEN EXISTS ( SELECT
                                          1
                                     FROM
                                          dbo.SHC
                                     WHERE
                                          SHC.SHCSTDT = M.SBRSTDT ) THEN 1
                       ELSE 0
                  END AS Expr1
            ) AS [Medical Problem] ,
            M.SBRRACE AS Race ,
            'n/a' AS [Race Description] ,
            M.SBRCGRD AS [Grade Level] ,
            'n/a' AS [Grade Level Description] ,
            M.SBRLEP AS [LEP Category Code] ,
            M.SBRLNELG AS [Lunch Type Code] ,
            'n/a' AS [Lunch Type Description] ,
            M.SBRLANG AS [Primary Language] ,
            'n/a' AS [Primary Langauge Description] ,
            M.SBRCSCHL AS [School Code] ,
            'BELL ELEMENTARY SCHOOL' AS [School Description] ,
            M.SBRPRIM AS [Special Ed Category Code] ,
            'n/a' AS [Special Ed Category Description] ,
            '***' AS [Medical Reason] ,
            ( CASE WHEN M.sbrcgrd = M.sbrlgrd THEN 1
                   ELSE 0
              END ) AS Retained ,
            M.SBRCHMRM AS [Home Room] ,
            (
              SELECT
                  TCHLNAME
              FROM
                  dbo.SST
              WHERE
                  ( SSTTCHR = (
                                SELECT
                                    SHRTCHR
                                FROM
                                    dbo.SHR
                                WHERE
                                    ( SHRHMRM = M.SBRCHMRM )
                                    AND ( SHRSCHL = '0032' )
                                    AND ( SHRFY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) ,
                                                            3 , 2) )
                              ) )
                  AND ( SSTSCHL = '0032' )
                  AND ( SSTSY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) , 3 ,
                                          2) )
            ) AS [Homeroom Teacher Last name] ,
            MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME
                     else ''
                END) AS [Contact 1 Name] ,
            MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME
                     else ''
                END) AS [Contact 2 Name] ,
            MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME
                     else ''
                END) AS [Contact 3 Name] ,
            MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME
                     else ''
                END) AS [Contact 4 Name]
      FROM
            dbo.SBR AS M
            LEFT OUTER JOIN (
                              select
                                    (
                                      select
                                          count(*)
                                      from
                                          dbo.sci i
                                      where
                                          i.scics < sci.scics
                                          and i.scistdt = sci.scistdt
                                    ) as seq ,
                                    *
                              from
                                    dbo.sci sci
                            ) AS SCI_1
                  ON M.SBRSTDT = SCI_1.SCISTDT
      WHERE
            ( M.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
            AND ( M.SBRCSCHL = '0032' )
      GROUP BY
            M.SBRSTDT ,
            M.SBRLAST ,
            M.SBRFIRST
      ORDER BY
            M.SBREXNBR ,
            M.SBRSTDT

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22766724
Yeah, any / all of those M.* columns would have to be in that group by given that sql statement - which is clearly not the way (ie group by).

However should be able to do something (if student ID is unique enough) like:

  SELECT TOP 100 PERCENT
            M.SBREXNBR AS [State ID] ,
            M.SBRSTDT AS [Student ID] ,
            M.SBRLAST AS [Last Name] ,
            M.SBRFIRST AS [First Name] ,
            M.SBRMIDDLE AS [Middle Initial] ,
            M.SBRSEX AS Gender ,
            M.SBRBIRTH AS DOB ,
            '(' + M.SBRAC + ') ' + M.SBREX + ' - ' + M.SBRNO AS [Phone Number] ,
            M.SBRRNBR + ' ' + M.SBRRDIR + ' ' + M.SBRRSTR + ' ' + M.SBRRTYPE
            + ' ' + M.SBRRAPT AS [Street Address] ,
            M.SBRRCITY AS City ,
            M.SBRRST AS State ,
            M.SBRRZIP5 AS Zipcode ,
            (
              SELECT
                  CASE WHEN EXISTS ( SELECT
                                          1
                                     FROM
                                          dbo.SHC
                                     WHERE
                                          SHC.SHCSTDT = M.SBRSTDT ) THEN 1
                       ELSE 0
                  END AS Expr1
            ) AS [Medical Problem] ,
            M.SBRRACE AS Race ,
            'n/a' AS [Race Description] ,
            M.SBRCGRD AS [Grade Level] ,
            'n/a' AS [Grade Level Description] ,
            M.SBRLEP AS [LEP Category Code] ,
            M.SBRLNELG AS [Lunch Type Code] ,
            'n/a' AS [Lunch Type Description] ,
            M.SBRLANG AS [Primary Language] ,
            'n/a' AS [Primary Langauge Description] ,
            M.SBRCSCHL AS [School Code] ,
            'BELL ELEMENTARY SCHOOL' AS [School Description] ,
            M.SBRPRIM AS [Special Ed Category Code] ,
            'n/a' AS [Special Ed Category Description] ,
            '***' AS [Medical Reason] ,
            ( CASE WHEN M.sbrcgrd = M.sbrlgrd THEN 1
                   ELSE 0
              END ) AS Retained ,
            M.SBRCHMRM AS [Home Room] ,
            (
              SELECT
                  TCHLNAME
              FROM
                  dbo.SST
              WHERE
                  ( SSTTCHR = (
                                SELECT
                                    SHRTCHR
                                FROM
                                    dbo.SHR
                                WHERE
                                    ( SHRHMRM = M.SBRCHMRM )
                                    AND ( SHRSCHL = '0032' )
                                    AND ( SHRFY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) ,
                                                            3 , 2) )
                              ) )
                  AND ( SSTSCHL = '0032' )
                  AND ( SSTSY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) , 3 ,
                                          2) )
            ) AS [Homeroom Teacher Last name] ,
 
           SCI_4.[Contact 1 Name] ,
           SCI_4.[Contact 2 Name] ,
           SCI_4.[Contact 3 Name] ,
           SCI_4.[Contact 4 Name] 
 
      FROM
            dbo.SBR AS M
            INNER JOIN (SELECT
                        SBR.SBRSTDT,
                        MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME else '' END) AS [Contact 1 Name],
                        MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME else '' END) AS [Contact 2 Name],
                        MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME else '' END) AS [Contact 3 Name],
                        MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME else '' END) AS [Contact 4 Name]
                  FROM  dbo.SBR
                  LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT
                  GROUP BY SBR.SBRSTDT) as SCI_4 on  M.SBRSTDT = SCI_4.SBRSTDT
 
      WHERE
            ( M.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
            AND ( M.SBRCSCHL = '0032' )
      ORDER BY
            M.SBREXNBR ,
            M.SBRSTDT

Open in new window

0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22770202
yeah both columns in the ORDER BY (M.SBREXNBR and M.SBRSTDT) are both unique, 2 'primary keys' if you will.

sbrexnbr is a field that uniquely identifies that student within the state of florida, sbrstdt is a field that uniquely identifies that student within this company. Why they used 2 unique fields is a really long story.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22770261
In which case, think that query should go pretty dang close (typos and D'Oh level syntax errors excepted).
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 35
Nested Case statement 4 37
Database Integrity 1 49
SQL Availablity Groups Shared Path 2 14
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question