none
Problem with completely nested query

    Question

    • Hi,

      I have this completely nested query:

      SELECT S.sname from S where
         S# in (select S# from SPJ where 
        P# in (select P# from P where P.pname = 'Bolt') 
        and
                 J# in (select J# from J where J.city= 'London')
        );

         but when I want to execute it, the execution starts and doesn't stop, in fact it doesn't execute. It says:"executing query" and nothing happens.

      Can anybody help me what is the problem please?


    • Thanks


    • Edited by pet06 Sunday, March 16, 2014 2:43 AM
    Sunday, March 16, 2014 2:42 AM

All replies

  •  but when I want to execute it, the execution starts and doesn't stop, in fact it doesn't execute. It says:"executing query" and nothing happens.

    Can anybody help me what is the problem please?

    Not much to go on with the information provided but my guess is that the tables are fairly large.  Make sure that the data types returned by the subqueries match the associated columns and that you have indexes (ideally unique and covering) on the columns specified in the WHERE clause IN and equality predicates.

    If you need additional help, please provide the DDL (CREATE TABLE statements, indexes) along with row counts and cardinality.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, March 16, 2014 2:58 AM
  • As Dan suggested, you need to look at column datatypes that are joined are same and you have appropriate indexes to support the query execution.

    You can tweak the query a bit and see how it works:

    SELECT S.sname from S where
       Exists (select 1 from SPJ where SPJ.S# = S.S# and Exists
      (select 1 from P where P.pname = 'Bolt' and P.P# = SPJ.P#) and exists 
      (select 1 from J where J.city= 'London' and J.J#=SPJ.J#)
     );

    Sunday, March 16, 2014 3:16 AM
  • What about using JOINS:

    SELECT S.sname from S 
    inner join SPJ ON s.S# =SPJ.S# 
    inner join P on SPJ.[P#]=P.[P#] AND P.pname = 'Bolt' 
    inner join J on SPJ.[J#]=J.[J#] AND J.city= 'London'
    
    --Or
    
    SELECT S.sname from S 
    inner join SPJ ON S.S# =SPJ.S# 
    inner join P on SPJ.[P#]=P.[P#] 
    inner join J on SPJ.[J#]=J.[J#] 
    WHERE P.pname = 'Bolt' AND J.city= 'London'

    Sunday, March 16, 2014 3:29 AM
  • You could try rewriting the query without the subqueries.  I THINK the following is equivalent, but of course, you would have to test it to be sure it returns the correct values.  But if it does, hopefully, it will run faster.

    Create Table #TempSPJ(S# <datatype> Primary Key);  -- make <datatype> the data type of S# in table S
    
    Insert #TempSPJ(S#)
    Select Distinct SPJ.S#
    From SPJ
    Inner Join P On SPJ.P# = P.P#
    Inner Join J ON SPJ.J# = J.J#
    Where P.pname = 'Bolt' And J.city = 'London';
    
    Select S.sname
    From S
    Inner Join #TempSPJ t On S.S# = t.S#;

    Of course, as Dan mentioned, good indexes will help the query performance.

    Tom

     
    Sunday, March 16, 2014 3:46 AM
  • Try to get just one row from any tables ok, like this :

    SELECT TOP 1 S.sname from S where
       S# in (select TOP 1 S# from SPJ where 
      P# in (select TOP 1 P# from P where P.pname = 'Bolt') 
      and
               J# in (select TOP 1 J# from J where J.city= 'London')

      );

    If the execution query done (its not necessary to get the data), so you must index your database
    Sunday, March 16, 2014 3:56 AM
  • -Is this a homework assignment with the famous Chris Date SPJ database? Too bad we do not have DDL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, March 16, 2014 3:11 PM