locked
SELECT TOP 1 in subquery RRS feed

  • Question

  • Hey guys,

    I'm trying to get the most recent appointment to be outputted with my job table and if there is no appointments for that job then show the job details and have the appointment details blank.

    A simplified version of my code follows...

    SELECT
    	DISTINCT job.JobID, Client.ClientID, 
    	Client.ClientName AS 'Client Name',
    	(SELECT TOP 1 AppointmentType, AptDateTime, FirstName + ' ' + Surname AS 'Attendant'
    	FROM
    		General.Appointment
    		INNER JOIN General.JobAppointment ON Appointment.AppointmentID = JobAppointment.AppointmentID
    	WHERE
    		JobAppointment.JobID = job.JobID
    	ORDER BY AptDateTime DESC)
    FROM	
    	Contract.Job job	
    	INNER JOIN Client.Client ON job.ClientID = Client.ClientID
    WHERE
    	job.FinalisedDate IS NULL
    ORDER BY
    	'Client Name'
    

    I'm receiving the error 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' and can't work out the source...

    Can anyone see where I'm going wrong?

    Cheers.

    Monday, April 18, 2011 7:52 AM

Answers

  • You need to rewrite you query. as you can only have one select column in your inner select statement. Use the Cross Apply or Outer Apply instead

     

    SELECT
    	DISTINCT job.JobID, Client.ClientID, 
    	Client.ClientName AS 'Client Name'
    	, X.AppointmentType, X.AptDateTime, X.FirstName + ' ' + X.Surname AS 'Attendant'
    FROM
    	Contract.Job job
    	INNER JOIN Client.Client ON job.ClientID = Client.ClientID
    	Outer Apply
    	(
    		SELECT TOP 1 *
    		FROM General.Appointment A
    			INNER JOIN General.JobAppointment J ON A.AppointmentID = J.AppointmentID
    		WHERE J.JobID = job.JobID
    		ORDER BY AptDateTime DESC
    	) X
    WHERE job.FinalisedDate IS NULL
    ORDER BY 'Client Name'
    
    

     





    Monday, April 18, 2011 8:05 AM

All replies

  • You need to rewrite you query. as you can only have one select column in your inner select statement. Use the Cross Apply or Outer Apply instead

     

    SELECT
    	DISTINCT job.JobID, Client.ClientID, 
    	Client.ClientName AS 'Client Name'
    	, X.AppointmentType, X.AptDateTime, X.FirstName + ' ' + X.Surname AS 'Attendant'
    FROM
    	Contract.Job job
    	INNER JOIN Client.Client ON job.ClientID = Client.ClientID
    	Outer Apply
    	(
    		SELECT TOP 1 *
    		FROM General.Appointment A
    			INNER JOIN General.JobAppointment J ON A.AppointmentID = J.AppointmentID
    		WHERE J.JobID = job.JobID
    		ORDER BY AptDateTime DESC
    	) X
    WHERE job.FinalisedDate IS NULL
    ORDER BY 'Client Name'
    
    

     





    Monday, April 18, 2011 8:05 AM
  • Hi Steven,

    You need to change this query to OUTER APPLY, as we may not have appointments for the job.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, April 18, 2011 5:04 PM
  • Right, Thanks Naomi.
    Monday, April 18, 2011 9:05 PM
  • Please post DDL. Learn to use ISO-11179 rules for the data element _names, avo_id needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

    Your singular table _names say they have only one row! It is also unusual to see a SELECT DISTINCT in proper DDL – not wrong, but a bad code smell.

    And we NEVER format data for display in the database side of a tiered architecture – the aliases and concatenation are just plain wrong.
     
    The job_id changes names from table to table.  A thing has one and only one name. I see the “job” is sometime a scalar columns and sometime a table. Did you ever have a basic data modeling course?

    Finally, three part table names is a really bad code smell. Like dead fish. A schema should be a complete data model and therefore in one database.

    Also, do you find “General” to be, well, vague? And why are job_appointments in a  totally different universe from appointments? What about appointment_type?

    Using TOP is also a really bad code smell. It say you talk like hillbilly who only knows T-SQL and cannot think in sets. You do not understand how a scalar subquery in a SELECT works – it is SCALAR!! You are shoving a table into a column.

    << I'm trying to get the most recent appointment to be outputted [sic] with my job table and if there is no appointments for that job then show the job details and have the appointment details blank [sic]. <<

    When I was on ANSI X3H2, we use to says SQL means “Scarily Qualifies as a Language” because it has no I/O of its own; it needs a host program. We use NULLs and not blanks -- you are still doing file system process in COBOL, but you are trying to write it in SQL.

    Here is a guess at what your query would be if you had correct DDL.

    SELECT J.job_id, C.client_id, C.client_first_name, C.client_sur_name,
           A.appointment_type, A.appointment_datetime
      FROM (SELECT *
              FROM Jobs AS J
             WHERE something_final_date IS NULL)
           LEFT OUTER JOIN
           (SELECT *
              FROM Appointments AS A, Clients AS C
             WHERE A.appointment_type = 'JOB'
               AND J.client_id = C.client_id)
           ON J.job_id = A.job_id;       
          
     >> Can anyone see where I'm going wrong? <<

    Almost everywhere :) But without DDL,who knws?

    --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
    Tuesday, April 19, 2011 1:38 AM