none
Select second query if first query returns no rows

    Question

  • Hi

    I need to return result of the second select statement if the first select statement returns nothing:

    -- First statement
    SELECT Salesmen.SalesmanID, Salesmen.FullName, 
      Salesmen.AssignedAppointments, Salesmen.Picture, 
      Appointments.Closed
    FROM Appointments INNER JOIN
      Salesmen ON 
      Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments
    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments
    

    Thursday, October 14, 2010 12:48 PM

Answers

  • David just forgot END

    IF EXISTS(
    SELECT Salesmen.SalesmanID,
      Salesmen.FullName,
      Salesmen.AssignedAppointments,
      Salesmen.Picture,
      Appointments.
      Closed
    FROM Appointments INNER JOIN Salesmen
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
     )
    BEGIN
    SELECT Salesmen.SalesmanID,
      Salesmen.FullName,
      Salesmen.AssignedAppointments,
      Salesmen.Picture,
      Appointments.
      Closed
    FROM Appointments INNER JOIN Salesmen
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments

    END
    ELSE

    BEGIN

    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments

    END


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by zipfeli Thursday, October 14, 2010 1:28 PM
    Thursday, October 14, 2010 1:23 PM
    Moderator
  • Using an IF possibly you can evaluate if rows exists or IF the count is > 0:

     

    IF EXISTS(
    	SELECT Salesmen.SalesmanID, 
    		  Salesmen.FullName, 
    		  Salesmen.AssignedAppointments, 
    		  Salesmen.Picture, 
    		  Appointments.
    		  Closed
    	FROM Appointments INNER JOIN Salesmen 
    	ON Appointments.SalesmanID = Salesmen.SalesmanID
    	WHERE (Appointments.Closed = 1)
    	ORDER BY Salesmen.AssignedAppointments)
    BEGIN
    SELECT Salesmen.SalesmanID, 
    	  Salesmen.FullName, 
    	  Salesmen.AssignedAppointments, 
    	  Salesmen.Picture, 
    	  Appointments.
    	  Closed
    FROM Appointments INNER JOIN Salesmen 
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments
    ELSE
    
    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments
    
    

    David Dye http://sqlsafety.blogspot.com/
    Thursday, October 14, 2010 12:57 PM
    Moderator

All replies

  • SELECT Salesmen.SalesmanID, Salesmen.FullName,
      Salesmen.AssignedAppointments, Salesmen.Picture,
      Appointments.Closed
    FROM Appointments INNER JOIN
      Salesmen ON
      Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments

    IF @@ROWCOUNT=0

    BEGIN
    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments

    END


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 12:53 PM
    Moderator
  • One method is to modify the query to UNION ALL both queries together and to add a WHERE NOT EXISTS clause into the second query based on the FROM and WHERE clause of the first query.  For EXAMPLE:

    SELECT Salesmen.SalesmanID, Salesmen.FullName, 
     Salesmen.AssignedAppointments, Salesmen.Picture, 
     Appointments.Closed
    FROM Appointments INNER JOIN
     Salesmen ON 
     Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments
    UNION ALL
    SELECT *
    FROM Salesmen
    where not exists
    ( SELECT 0
     FROM Appointments 
     INNER JOIN Salesmen 
     ON Appointments.SalesmanID = Salesmen.SalesmanID
     WHERE (Appointments.Closed = 1)
    )
    

    Another alternative is to use IF / ELSE logic

    EDIT:

    Hello, David.  How have you been?

    Thursday, October 14, 2010 12:54 PM
    Moderator
  • Using an IF possibly you can evaluate if rows exists or IF the count is > 0:

     

    IF EXISTS(
    	SELECT Salesmen.SalesmanID, 
    		  Salesmen.FullName, 
    		  Salesmen.AssignedAppointments, 
    		  Salesmen.Picture, 
    		  Appointments.
    		  Closed
    	FROM Appointments INNER JOIN Salesmen 
    	ON Appointments.SalesmanID = Salesmen.SalesmanID
    	WHERE (Appointments.Closed = 1)
    	ORDER BY Salesmen.AssignedAppointments)
    BEGIN
    SELECT Salesmen.SalesmanID, 
    	  Salesmen.FullName, 
    	  Salesmen.AssignedAppointments, 
    	  Salesmen.Picture, 
    	  Appointments.
    	  Closed
    FROM Appointments INNER JOIN Salesmen 
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments
    ELSE
    
    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments
    
    

    David Dye http://sqlsafety.blogspot.com/
    Thursday, October 14, 2010 12:57 PM
    Moderator
  • Wow that was fast Uri, thank you!

    Though, I got the same 2 results as with my code. I need to have only the result of the second if the first returns nothing.

    Printscreen

    http://img844.imageshack.us/img844/8712/1014201030503pm.png

     

     

     

    Thursday, October 14, 2010 1:06 PM
  • Yep, you are right , I was under impression of below logic

    create table #t (c int)
    create table #t1 (c int)

    insert into #t1 values (10)


    select * from #t
    if @@rowcount>0
    begin
    select * from #t1
    end

    Well, using David solution is probably best option

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 1:16 PM
    Moderator
  • Yep, you are right , I was under impression of below logic

    create table #t (c int)
    create table #t1 (c int)

    insert into #t1 values (10)


    select * from #t
    if @@rowcount>0
    begin
    select * from #t1
    end

    Well, using David solution is probably best option

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 1:16 PM
    Moderator
  • Hi David

    Thank you, trying your code I receive error:

    Msg 1033, Level 15, State 1, Line 11
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
    Msg 156, Level 15, State 1, Line 23
    Incorrect syntax near the keyword 'ELSE'.
    Msg 102, Level 15, State 1, Line 28
    Incorrect syntax near 'AssignedAppointments'.
    
    

    Thursday, October 14, 2010 1:19 PM
  • David just forgot END

    IF EXISTS(
    SELECT Salesmen.SalesmanID,
      Salesmen.FullName,
      Salesmen.AssignedAppointments,
      Salesmen.Picture,
      Appointments.
      Closed
    FROM Appointments INNER JOIN Salesmen
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
     )
    BEGIN
    SELECT Salesmen.SalesmanID,
      Salesmen.FullName,
      Salesmen.AssignedAppointments,
      Salesmen.Picture,
      Appointments.
      Closed
    FROM Appointments INNER JOIN Salesmen
    ON Appointments.SalesmanID = Salesmen.SalesmanID
    WHERE (Appointments.Closed = 1)
    ORDER BY Salesmen.AssignedAppointments

    END
    ELSE

    BEGIN

    -- Second statement
    SELECT *
    FROM Salesmen
    ORDER BY AssignedAppointments

    END


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by zipfeli Thursday, October 14, 2010 1:28 PM
    Thursday, October 14, 2010 1:23 PM
    Moderator
  • Thank you David, thank you Uri, you're so kind. Many, many thanks.
    Thursday, October 14, 2010 1:28 PM