Correlated subquery SQL not working

[organicg]

I have a 'search' stored proc. and I cannot get the 'If-Else' part of this 'WHERE' clause to work. What is wrong with this syntax? I'm trying to use the If-Else to determine which subquery to use to dynamically retrieve a User.UserID to plug into the outer query. How do I successfully built the SQL string?

SET @WHERE3 = 'AND
(select Users.ZipCode from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
INNER JOIN Trips on Trips.TripID = TripsUsers.TripID
where TripsUsers.UserID =
If (select count(*) from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0
(select UserID from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1)
Else
(Select TOP 1 UserID from TripsUsers where TripsUsers.TripID=Trips.TripID)

 

[organicg]

thank you again, mjia.
I suspected that. That without including Trips table, it just uses the outer query reference to Trips table. Strange that the results change when including it in those 6 subqueries, though. I'd think it would just be a more explicit clarification.

OK, here's the short(est) English description of the SPROC_SEARCH I can give:
Return one record for each trip(there will always be at least one User on the trip)...
if it satisfies the search params entered by website user...
including a 'distance' field for each trip returned, that represents the distance from the @ZipFrom parameter to the zip code of the User on the trip who is the driver(TripsUsers row where IsDriver=1 for that TripID)...
or if there is no driver on the trip, the zip code of the User who is the first row returned in TripsUsers for this TripID.

That last part about which zip code to assoc. with the trip is where you helped me by inserting a CASE statement to 'select ZipDistances.Distance', instead of just my old way of 'select ZipDistances.Distance' and then trying to specify the correct User.ZipCode in the Where clause at the bottom of the SP.
Remember that ZipDistances has all the distances already calculated and just needs to return the distance from the correct row.
I really hope this makes it clear what is trying to be accomplished here.

 

[organicg]

mjia,
I have modified the CASE statement you proposed and appear to be getting the correct results in Query Analyer!!! One record for each trip is returned with the correct zip code specific distance!!! (However, I get this error when running it on the localhost website:
A field or property with the name 'Distance' was not found on the selected datasource. Hmm, I'm not sure why I get that, but I'll have to come back to this tonight. Thank you so much for your help.

Here's what the new, slightly changed chunk of SQL looks like now:

CASE 
	WHEN (select count(*) from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0 THEN
	 (select ZipDistances.Distance as Distance from Users
		INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
		INNER JOIN ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
		WHERE TripsUsers.IsDriver=1 
		AND Trips.TripID = TripsUsers.TripID
        AND ZipCodeFrom =  ''' + @ZipFrom + '''  AND Distance <= ' + cast(@Distance as varchar(5)) + ')
	
	ELSE
	 (select top 1 ZipDistances.Distance as Distance from Users
		INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
		INNER JOIN ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
		WHERE Trips.TripID = TripsUsers.TripID
		AND ZipCodeFrom =  ''' + @ZipFrom + '''  AND Distance <= ' + cast(@Distance as varchar(5)) + ')
 END

 

[maswien]

DECLARE @csvResortIDs [varchar](10)
DECLARE @csvTripTypes [varchar](10)
DECLARE @ZipFrom [varchar](10)
DECLARE @Distance [varchar](10)
Declare @StartDate [varchar](20)
DECLARE @EndDate [varchar](20)
DECLARE @NeedsA char(1)
DECLARE @ShowFull char(1)


SELECT @csvResortIDs = '2'
SELECT @csvTripTypes = '3'
SELECT @ZipFrom = '80001'
SELECT @Distance = '700'
SELECT @StartDate = '2000-01-01'
SELECT @EndDate = '2004-01-01'
SELECT @NeedsA = 1
SELECT @ShowFull = 1

DECLARE @WHERE1 [varchar](1000)
DECLARE @WHERE2 [varchar](1000)
DECLARE @WHERE3 [varchar](1000)
DECLARE @WHERE4 [varchar](1000)
Declare @UserID [int]
DECLARE @SQL [varchar](4000)

SET @WHERE1= ''
SET @WHERE2= ''
SET @WHERE3= ''
SET @WHERE3= ''
set @UserID = 0 --DEFAULT value
SET @SQL =
'SELECT Trips.TripID, Resort.Name as Resort, Trips.TripDate as [Date], Trips.Description as [More Info],
CASE
WHEN ((SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) >= Trips.Maxpassengers) THEN ''FULL''
ELSE cast((SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) as char(1)) + ''/'' + cast(Trips.MaxPassengers as char(1))
END as [Ppl/Max],
CASE (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1)
WHEN 0 THEN ''NO''
WHEN 1 THEN ''YES''
END as [Has Driver],
TripTypes.TripTypeName as [Who''s Welcome],

case when (select count(*) from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0
then (select top 1 ZipDistances.Distance as Distance from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where TripsUsers.IsDriver=1 and Trips.TripID = TripsUsers.TripID
and ZipCodeFrom = ''' + @ZipFrom + ''' AND Distance <= ' + cast(@Distance as varchar(5)) + ')
Else

(select top 1 ZipDistances.Distance as Distance from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where Trips.TripID = TripsUsers.TripID and ZipCodeFrom = ''' + @ZipFrom + ''' AND Distance <= ' + cast(@Distance as varchar(5)) + ')

end


FROM Trips INNER JOIN Resort ON Resort.ResortID = Trips.ResortID
INNER JOIN TripTypes ON Trips.TripTypeID = TripTypes.TripTypeID

WHERE
((' + cast(@NeedsA as char(1)) + ' = 0) AND not exists (SELECT * from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) ) or --Needs a driver, but doesn''t have one
((' + cast(@NeedsA as char(1)) + ' = 1) AND (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) <= (Trips.MaxPassengers-2)) or --Definitely has room for a passenger because there''s at least 2 spots free
((' + cast(@NeedsA as char(1)) + ' = 1) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) = (Trips.MaxPassengers-1) AND exists (select * from TripsUsers where TripsUsers.TripID = Trips.TripID and TripsUsers.IsDriver = 1) or --Needs a passenger, only 1 spot free in the car, but theres already a driver
((' + cast(@NeedsA as char(1)) + ' = 2) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) < Trips.MaxPassengers) or --has an open spot in the car
((' + cast(@ShowFull as char(1)) + ' = 1) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) = Trips.MaxPassengers)) AND --Show trips that are FULL
(Trips.Tripdate >= ''' + cast(@StartDate as varchar(30)) + ''') AND
(Trips.Tripdate <= ''' + cast(@EndDate as varchar(30)) + ''') '

IF @csvResortIDs <> '' --These will never be '' because these dropdowns always have a value selected. Just add the AND clauses to @SQL, don't check for ''
SET @WHERE1 = 'AND Trips.ResortID IN(' + @csvResortIDs + ') '
Else
SET @WHERE1 = 'AND Trips.ResortID = 30 '
IF @csvTripTypes <> ''
SET @WHERE2 = 'AND Trips.TripTypeID IN(' + @csvTripTypes + ') '
Else
SET @WHERE2 = 'AND Trips.TripTypeID = 4 '



SET @SQL = @SQL + @WHERE1 + @WHERE2 + ' order by Trips.TripDate ASC; '
print @SQL
--EXECUTE(@SQL)


Run this SP, you will get following SQL printed:


SELECT Trips.TripID, Resort.Name as Resort, Trips.TripDate as [Date], Trips.Description as [More Info],
CASE
WHEN ((SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) >= Trips.Maxpassengers) THEN 'FULL'
ELSE cast((SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) as char(1)) + '/' + cast(Trips.MaxPassengers as char(1))
END as [Ppl/Max],
CASE (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1)
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
END as [Has Driver],
TripTypes.TripTypeName as [Who's Welcome],

case when (select count(*) from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0
then (select top 1 ZipDistances.Distance as Distance from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where TripsUsers.IsDriver=1 and Trips.TripID = TripsUsers.TripID
and ZipCodeFrom = '80001' AND Distance <= 700)
Else

(select top 1 ZipDistances.Distance as Distance from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where Trips.TripID = TripsUsers.TripID and ZipCodeFrom = '80001' AND Distance <= 700)

end


FROM Trips INNER JOIN Resort ON Resort.ResortID = Trips.ResortID
INNER JOIN TripTypes ON Trips.TripTypeID = TripTypes.TripTypeID

WHERE
((1 = 0) AND not exists (SELECT * from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) ) or --Needs a driver, but doesn't have one
((1 = 1) AND (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID) <= (Trips.MaxPassengers-2)) or --Definitely has room for a passenger because there's at least 2 spots free
((1 = 1) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) = (Trips.MaxPassengers-1) AND exists (select * from TripsUsers where TripsUsers.TripID = Trips.TripID and TripsUsers.IsDriver = 1) or --Needs a passenger, only 1 spot free in the car, but theres already a driver
((1 = 2) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) < Trips.MaxPassengers) or --has an open spot in the car
((1 = 1) AND (SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID) = Trips.MaxPassengers)) AND --Show trips that are FULL
(Trips.Tripdate >= '2000-01-01') AND
(Trips.Tripdate <= '2004-01-01') AND Trips.ResortID IN(2) AND Trips.TripTypeID IN(3) order by Trips.TripDate ASC;



Run this SQL, you will get results:

tripid ... distance
------- ---- --------
47 ... 21.9
48 ... 22.5

2 rows returned.

I noticed NeedsA can be 0,1,2 , in this case it's 2 then it means show the trips that need n passagers or a driver.
(n >= 1). I think the answer is correct because trip 47 and 48 all satisfies following conditions:

1. resortID = 2
2. TripTypes = 3
3. 47 assoc with user 2 who is a driver and his zipcode
is 80002 and the distance for him is 21.9, 48 assoc
with user 3 who is not a driver (because there isn't a
driver for this trip) and his zipcode is 80003 and the
distance for him is 22.5

You can try the SQL I posted in this comment on your machine
and see if it's the correct one.

 

[maswien]

I wrote the last comment before I saw your latest post.
So it's kind of duplicate, you found the error in the
SP I give you, yes, your modified one is the correct one
which takes out the unneccessary 'inner join trips' in
the else clause. You already got it!!!

I think you can debug the ASP error follow this steps:

1. run SP with the parameters

2. if 1 works run that SP with parameters in ASP

3. if 2 works, then something wrong with the input
or something wrong when ASP pass a command string to
the datasource.

 

[zhavic]

Hi, I am back from very long work

Here is little optimized example I am working on :
( try to run it )

SELECT *
FROM Trips 
	/* simple create counts of all users in each trip and also count of drivers ( may be good for machines with more drivers :-) ) */
	INNER JOIN ( SELECT TripID, COUNT(*) AS Count_All, SUM( CASE WHEN IsDriver = 1 THEN 1 ELSE 0 END ) AS Count_IsDriver FROM TripsUsers GROUP BY TripID ) AS TripUsersCount ON TripUsersCount.TripID = Trips.TripID
	INNER JOIN Resort ON Resort.ResortID = Trips.ResortID
    INNER JOIN TripTypes ON Trips.TripTypeID = TripTypes.TripTypeID
    INNER JOIN TripsUsers ON Trips.TripID = TripsUsers.TripID
    INNER JOIN Users ON TripsUsers.UserID = Users.UserID
    INNER JOIN ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
WHERE ( TripUsersCount.Count_IsDriver > 0 AND TripsUsers.IsDriver = 1 ) -- there is driver ( or more drivers :-) ) in the trip
	  -- no driver in the trip, so take first user in that trip
	  OR ( TripUsersCount.Count_IsDriver = 0 AND Users.UserID = ( SELECT TOP 1 UserID 
																	FROM TripsUsers AS WithoutDriver 
																	WHERE WithoutDriver.TripID = Trips.TripID 
                                                                )
         )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

Hmmm, don't now if this is what you are looking for, but try it

ALTER PROCEDURE SPROC_SEARCH
(  @csvResortIDs [varchar](63),
   @csvTripTypes [varchar](10),
   @ZipFrom [char](5),
   @Distance [smallint],
   @StartDate [smalldatetime],
   @EndDate [smalldatetime],
   @NeedsA [tinyint],    --0=Driver|1=Passenger|2=Either
   @ShowFull [bit]
)

AS

DECLARE @cSQL [nvarchar](4000)

DECLARE @cFilter_for_Resort nvarchar(100),
		@cFilter_for_TripTypes nvarchar(100)

IF @csvResortIDs <> ''
    SET @cFilter_for_Resort = 'AND Resort.ResortID IN(' + @csvResortIDs + ') '
ELSE
    SET @cFilter_for_Resort = 'AND Resort.ResortID = 30 '

IF @csvTripTypes <> ''
    SET @cFilter_for_TripTypes = 'AND TripTypes.TripTypeID IN(' + @csvTripTypes + ') '
ELSE
    SET @cFilter_for_TripTypes = 'AND TripTypes.TripTypeID = 4 '


SET @cSQL =
'SELECT  Trips.TripID,
		Resort.Name AS Resort, 
		Trips.TripDate AS [Date], 
		Trips.Description AS [More Info],
		CASE
		    WHEN TripUsersCount.Count_All >= Trips.Maxpassengers THEN ''FULL''
		    ELSE CAST( TripUsersCount.Count_All AS char(1) ) + ''/'' + CAST( Trips.MaxPassengers AS char(1) )
		END AS [Ppl/Max],
		CASE TripUsersCount.Count_IsDriver
		    WHEN 0 THEN ''NO''
		    WHEN 1 THEN ''YES''
		END AS [Has Driver],
		TripTypes.TripTypeName as [Who''s Welcome],
		ZipDistances.Distance as Distance
,TripUsersCount.* 
FROM Trips 
	INNER JOIN ( SELECT TripID, COUNT(*) AS Count_All, SUM( CASE WHEN IsDriver = 1 THEN 1 ELSE 0 END ) AS Count_IsDriver FROM TripsUsers GROUP BY TripID ) AS TripUsersCount ON TripUsersCount.TripID = Trips.TripID
	INNER JOIN Resort ON Resort.ResortID = Trips.ResortID ' + @cFilter_for_Resort + '
	INNER JOIN TripTypes ON Trips.TripTypeID = TripTypes.TripTypeID ' + @cFilter_for_TripTypes + '
	INNER JOIN TripsUsers ON Trips.TripID = TripsUsers.TripID
	INNER JOIN Users ON TripsUsers.UserID = Users.UserID
	INNER JOIN ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo AND ZipDistances.ZipCodeFrom = @ZipFrom AND ZipDistances.Distance < @Distance
WHERE (  ( TripUsersCount.Count_IsDriver > 0 AND TripsUsers.IsDriver = 1 ) -- there is driver ( or more drivers ? :-) ) in the trip
	  -- no driver in the trip, so take first user in that trip
	  OR ( TripUsersCount.Count_IsDriver = 0 AND Users.UserID = ( SELECT TOP 1 UserID 
																	FROM TripsUsers AS WithoutDriver 
																	WHERE WithoutDriver.TripID = Trips.TripID 
	                                                            )
	     )
      )
	
	-- filter conditions
	AND Trips.Tripdate BETWEEN @StartDate AND @EndDate
	AND ( @NeedsA = 0 AND TripUsersCount.Count_IsDriver = 0 OR   --Needs a driver, but doesn''t have one
		  @NeedsA = 1 AND TripUsersCount.Count_All <= Trips.MaxPassengers - 2 OR   --Definitely has room for a passenger because there''s at least 2 spots free
		  @NeedsA = 1 AND TripUsersCount.Count_IsDriver = 1 AND TripUsersCount.Count_All <= Trips.MaxPassengers - 1 OR   --Needs a passenger, only 1 spot free in the car, but theres already a driver
		  @NeedsA = 2 AND TripUsersCount.Count_All < Trips.MaxPassengers OR   --has an open spot in the car
		  @ShowFull = 1 AND TripUsersCount.Count_All = Trips.MaxPassengers
	    )
ORDER BY Trips.TripDate ASC'

EXECUTE sp_executesql 
			@cSQL,
			N'@StartDate datetime,@EndDate datetime,@NeedsA [tinyint],@ShowFull [bit],@ZipFrom [char](5),@Distance [smallint]',
			@StartDate,
			@EndDate,
			@NeedsA,
			@ShowFull,
			@ZipFrom,
			@Distance

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[organicg]

mjia, the problem was that the distance column was not named. QA returned the results but the column heading was 'no column name'. When I bind the results to my grid in ASP.NET, I bind it to the named column 'distance'. So I just added 'as [Distance]' after the end of our famous CASE statement.
Thanks again.

Zhavic, your first SQL sample returns lots and lots of columns, more than I need, however, all values are correct, including the troublesome distance column!
The second SQL sample, however, gives me an error. I added 'execute @cSQL' to the bottom, and set params and called it from QA, without using your sp_executesql section. It says:
&quot;The name 'SELECT Trips.TripID, Resort.Name...&quot;
but it doesn't say anything else.
Since my original problem is solved and I have a working SP, I'm very happy, and optimizing the SP even more would be great, however don't feel obligated to keep working on this.