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)

 

[LogicalmanUS]

organicglenn,

Try this :

SET @WHERE3 = ''
SET @WHERE3 = @WHERE3 + ' AND (select Users.ZipCode from Users INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID '
SET @WHERE3 = @WHERE3 + ' INNER JOIN Trips on Trips.TripID = TripsUsers.TripID '
SET @WHERE3 = @WHERE3 + ' where TripsUsers.UserID = '

If (select count(*) from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0
BEGIN
SET @WHERE3 = @WHERE3 + ' (select UserID from TripsUsers where TripsUsers.TripID=Trips.TripID and IsDriver=1) '
END
ELSE
BEGIN
SET @WHERE3 = @WHERE3 + ' (Select TOP 1 UserID from TripsUsers where TripsUsers.TripID=Trips.TripID) '
END

When creating a SQL strinf, you need to include the IF-ELSE statements inside the string itself, but use it to form the string.

Logicalman

 

[organicg]

Thanks. I did what you suggested, and I can get the PRINT @SQLstring statement to print and the SQL statement looks good to me, but EXECUTE @SQLstring tells me 'incorrect syntax by the last ')'. Do you see something I missed?(I assure you I'm slaving over this and not just firing back an easy question at you)

(here's the later part of the WHERE clause)
 ...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 = (SELECT TripsUsers.UserID from TripsUsers, Trips where TripsUsers.TripID=Trips.TripID and IsDriver=1)) IN(SELECT ZipCodeTo from ZipDistances WHERE ZipCodeFrom = '80001' AND Distance <= 700)
 AND ZipDistances.ZipCodeFrom = '80001') order by Trips.TripDate ASC;

I'm confused by something. I think I understand how correlated subqueries work, with SQL Server substituting in different values for whatever is the current row it's working on. However, with the @SQLstring building statement you gave me, I don't understand. Will the correct subquery string be appended by the If-Else statement for each row that's being analyzed? I hope that's clear.

 

[LogicalmanUS]

organicglenn,

Taking a quick look at the slice of code you posted shows that you have an imbalance of parens (3 opening ansd 4 closing).

Does the last paren corrolate with an opening one earlier on in the WHERE clause?

Don't worry about asking questions, however easy or hard they may be, that's what we are on here for, to help where we can. I only draw the line at obvious total 'homework' questions (like What does RDBMS mean, give examples) which I refuse to answer.

Logicalman

 

[organicg]

It's amazing how when you've been staring at code for too long, you simply overlook the obvious. I counted, and counted...I should take more breaks and come back to the code.
Logicalman, you are my hero today...and yesterday. The sproc finally works...again!
In college(10yrs ago) my friends said I was the most logical person, but I guess I've been supplanted from that title.
Thanks a bunch.

 

[LogicalmanUS]

organicglenn,

Thanks for the kind words, I know what it's like finding those pesky perens!!

Anytime I can assist.

Logicalman

 

[organicg]

OK, I got excited too soon. Upon closer inspection of the SPROC results, instead of returning one row for each trip with the correct distance displayed(calc'd as the distance between the Users.ZipCode value that you helped me dynamically associate with the trip, and a zip code entered in the search) the SPROC returns one row for each user on the trip (rows in TripsUsers), and displays a distance from each Users zip code to the zip entered in the search.
I'll include the complete SPROC...geez it's hard to read when posted...the part you helped me with is "@WHERE3=" near the bottom.
Is there something wrong with, or missing from the logic I'm using? I'm also curious, do you think this SPROC is poorly written?

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 @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],
 ZipDistances.Distance as Distance

FROM Trips 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
(((' + cast(@NeedsA as char(1)) + ' = 0) AND (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 0) 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  (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 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 @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, Trips where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0
 SET @WHERE3 = @WHERE3 + '(SELECT TripsUsers.UserID from TripsUsers, Trips where TripsUsers.TripID=Trips.TripID and IsDriver=1))'
Else
 SET @WHERE3 = @WHERE3 + '(SELECT TOP 1 TripsUsers.UserID from TripsUsers, Trips where TripsUsers.TripID=Trips.TripID))'

 SET @WHERE3 = @WHERE3 + ' IN(SELECT ZipCodeTo from ZipDistances WHERE ZipCodeFrom = ''' + @ZipFrom + ''' AND Distance <= ' + cast(@Distance as varchar(5)) + ')
 AND ZipDistances.ZipCodeFrom = ''' + @ZipFrom + ''''	--this is needed because Distance is one of the SELECTed fields and the INNER JOIN with Trips is a 1:656 relationship otherwise

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

 

[organicg]

This SPROC returns too many rows. It's a 'search' procedure(takes about 7 params) that returns trips posted on a carpooling site. The three tables of significance(to this problem, I think, though others are joined) are: Users, TripsUsers, and Trips. Appropriately:
Users table stores a record for each registered user
Trips table stores a row for each trip created
TripsUsers stores a row for each user on a trip
My problem(as described in my 9/23 post)is that the search results contain duplicate records for a trip...one for each user on the trip, instead of just one record for the trip.

The entire SPROC is included above(9/23) but it's the "@WHERE3" clause that related to this issue.


Additional info that may help:
There's a zip code distance calculation performed also, from the zip entered in the search - to the zip of the driver on the trip(or someone else, if there's no driver). ZipDistances table is joined, and stores all possible zip-to-zip distances ALREADY calculated. This distance is one of the fields returned by the SPROC. The duplicate rows are displaying the calculated distance to each User's zip code, but all other Trip fields for a Trip are duplicates. I hope this makes sense, and isn't too long winded.

 

[zhavic]

organicglenn,

I'm also curious, do you think this SPROC is poorly written?

I can say: this query is written based on experiences that you have, but it can be written more optimized and readable.

But for best solution, we need some sample of data you have,
desired result from that datas and structure of tables that are used in that query, so we can create them, insert some data, change that query and run it, and then see if it return result you want.

I you don't want or can't post it there, no mather, but than it takes more time to get this to the right way

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.

 

[LogicalmanUS]

organicglenn,

Hmm, I've spent the last 10 minutes looking at the results of the SProc (I recreated it locally and got the printout in QA), and I've now got a brain-ache!!

OK. Right. First off, you say you are returning multiple rows based on tripusers rather than simply on Trips, and you also say that the distances returned are particular to each of those tripusers records, which would indicate that you will need to aggregate the results, by SUMming the distances and GROUP By the columns that repeat the same data.

The SUM would be on column 'ZipDistances.Distance as Distance'. All Columns returned in the first part of the statement (from SELECT Trips.TripID ... to TripTypes.TripTypeName as [Who''s Welcome], will need to be included in the aggregate clause.

But, as zhavic rightly states, it would be easier to test this hypothesis using real data. For this we would require table schemas and sample data.

In luei of this, can you state whether the results returned are both the same in the following cases:

Select Count(*) >= Trips.Maxpassengers
Select Count(*) < Trips.Maxpassengers

(This forms part of the first CASE statement)

Logicalman

 

[organicg]

Thanks for the willingness to dig in to this one.
I used 'Generate SQL Script' to provide a .sql script that is pasted below for you to create the DB with, however I'm unsure how to best provide you with sample data. I will just paste a few rows of data in here, but let me know if there's another way you'd like me to provide sample data.
First, Logicalman, I don't understand why I'd want to SUM the ZipDistances.Distance fields. The only significance that column has is when the ZipDistances.Distance for the appropriate row is associated with a trip. Adding these Distances removes the significance of the value.
2nd, you asked about:
Select Count(*) >= Trips.Maxpassengers
Select Count(*) < Trips.Maxpassengers
The purpose of that CASE stmt is just to print 'FULL' if the trip has 'Maxpassengers' # of Users already on it, but to print 'current capacity/max capacity' in the result grid if the trip is not 'FULL'. But it doesn't(shouldn't) affect the Trips returned from DB, just how that one column is rendered.
OK, here's all the info.

/*Start of .sql script - the CREATE TABLE blocks have descriptions of each table*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Trips_Resort]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Trips] DROP CONSTRAINT FK_Trips_Resort
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Trips_TripTypes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Trips] DROP CONSTRAINT FK_Trips_TripTypes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripInfo_Trips]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripInfo] DROP CONSTRAINT FK_TripInfo_Trips
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripsUsers_Trips]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripsUsers] DROP CONSTRAINT FK_TripsUsers_Trips
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripInfo_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripInfo] DROP CONSTRAINT FK_TripInfo_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripsUsers_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripsUsers] DROP CONSTRAINT FK_TripsUsers_Users
GO

/****** Object:  Table [dbo].[Resort]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Resort]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Resort]
GO

/****** Object:  Table [dbo].[TripInfo]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripInfo]
GO

/****** Object:  Table [dbo].[TripTypes]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripTypes]
GO

/****** Object:  Table [dbo].[Trips]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trips]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Trips]
GO

/****** Object:  Table [dbo].[TripsUsers]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripsUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripsUsers]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

/****** Object:  Table [dbo].[ZipDistances]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZipDistances]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZipDistances]
GO

/****** Object:  Table [dbo].[ZipMaster]    Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZipMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZipMaster]
GO

/****** Object:  Table [dbo].[Resort] This table stores the names of the resorts assoc. with trips, and is a search param.  Data is fixed******/
CREATE TABLE [dbo].[Resort] (
	[ResortID] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[TripInfo]   This table stores any text message the Users on a trip want to say to the other users on a trip******/
CREATE TABLE [dbo].[TripInfo] (
	[TripID] [int] NOT NULL ,
	[TimeStamp] [datetime] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[Message] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[TripTypes] 3 fixed rows ******/
CREATE TABLE [dbo].[TripTypes] (
	[TripTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[TripTypeName] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Trips] One record for each Trip created by a User ******/
CREATE TABLE [dbo].[Trips] (
	[TripID] [int] IDENTITY (1, 1) NOT NULL ,
	[TripDate] [smalldatetime] NOT NULL ,
	[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ResortID] [tinyint] NOT NULL ,
	[MaxPassengers] [tinyint] NOT NULL ,
	[TripTypeID] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[TripsUsers] One record for each user on a Trip(TripID & UserID is composite key)******/
CREATE TABLE [dbo].[TripsUsers] (
	[TripID] [int] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[IsDriver] [bit] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Users]One record for each User registered to use the web site******/
CREATE TABLE [dbo].[Users] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ReceiveEmailsFromSkiCarpool] [bit] NOT NULL ,
	[Phone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExposePhone] [bit] NOT NULL ,
	[Age] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReceiveEmailsFromPartners] [bit] NOT NULL ,
	[Gender] [bit] NOT NULL ,
	[MemberSince] [smalldatetime] NULL ,
	[EmailFormat] [bit] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[ZipDistances] 865 x 865 rows of data - stores the distance from each zip code in Colorado to EVERY OTHER zip code in the state so that no calculations are ever done when Users search for trips******/
CREATE TABLE [dbo].[ZipDistances] (
	[ZipCodeFrom] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ZipCodeTo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Distance] [decimal](4, 1) NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[ZipMaster] ~865 rows - one for each zip code in Colorado ******/
CREATE TABLE [dbo].[ZipMaster] (
	[Zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Name] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Lat] [decimal](8, 6) NOT NULL ,
	[Long] [decimal](9, 6) NOT NULL 
) ON [PRIMARY]
GO
/*End of .sql script*/

Here's some sample data for tables:
Users:
 1  billybob  1234  80001  skins@bwn.com  1  1  1  0  1
 2  me        mypwd 80002  me@me.com	  1  1	1  1  1
 3  you        3pwd  80003  you@me.com	  1  1	1  1  1

Resorts:
 1   Copper
 2   Keystone

Trips:
 46	1/1/2003	testtrip1	1	4	3
 47	2/2/2003	testtrip2	2	4	3
 48	2/2/2003	testtrip3	2	4	3

TripsUsers:
 46  1  0
 46  2  0
 46  3  1
 47  1  0
 47  2  1
 48  3  0

TripTypes:
 1  Skiers Only
 2  Boarders Only
 3  Skiers and Boarders

ZipDistances:
 80001	80001	0
 80001	80002	21.9
 80001	80003	22.5

ZipMaster and TripInfo tables should not need any sample data.

And here are sample parameters:
SELECT @csvResortIDs = '1,2'
SELECT @csvTripTypes = '1,2,3'
SELECT @ZipFrom = '80001'
SELECT @Distance = '700'
SELECT @StartDate = '1/1/2000'
SELECT @EndDate = '1/1/2004'
SELECT @NeedsA = 2
SELECT @ShowFull = 1

Let me know if I you need anything else at all. Thanks so much.

 

[maswien]

I don't understand in the last part of the where cluase:


(select Users.ZipCode from Users
INNER JOIN TripsUsers on Users.UserID = TripsUsers.UserID
INNER JOIN Trips on Trips.TripID = TripsUsers.TripID
where TripsUsers.UserID = (SELECT TripsUsers.UserID from TripsUsers, Trips where TripsUsers.TripID=Trips.TripID and IsDriver=1))
IN (SELECT ZipCodeTo from ZipDistances WHERE ZipCodeFrom = '80001' AND Distance <= 700 )


What it means? Please tell me what do you want to do
using this filter?

Another thing, if you just want the information for trips
you shouldn't include ZipDistances.Distance as Distance
in the SQL because that is user specific column, taking
out that column, you will be able to get a resultset
without duplicate rows using 'distinct' or 'group by'.

 

[maswien]

You can try this modified SP, it should returns the desirable answer, please check the quotes and parens.


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 @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],
ZipDistances.Distance as Distance

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 (SELECT count(*) from TripsUsers (nolock), trips WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 0) or --Needs a driver, but doesn''t have one
((' + cast(@NeedsA as char(1)) + ' = 1) AND (SELECT count(*) from TripsUsers (nolock), trips 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, trips WHERE TripsUsers.TripID = Trips.TripID) = (Trips.MaxPassengers-1) AND (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 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, trips 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, trips 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 '

If (select count(*) from TripsUsers, Trips where TripsUsers.TripID=Trips.TripID and IsDriver=1) > 0

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

Else

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

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

 

[organicg]

Thanks for responding Mjia.
Here's what I wrote just before you responded with a new sproc to test:
The task/filter I'm trying to perform is to associate a ZipDistances.Distance with a trip, based on a dynamically chosen User who is on that trip. So I cannot remove ZipDistances table from the join, I believe. Here's the idea, this is a carpooling website and this is the 'search for posted trips' stored procedure. ALL trips returned by search have at least one user on them, and need to return a distance from zip code entered in search TO the zip code of the dynamically chosen User. Dynamically choosing which User's zip to assoc. with the trip was the tricky part of the @WHERE3 clause...the subquery in the chunk you asked about. It there IS a driver on the trip then select TripsUsers.UserID where IsDriver=1, otherwise Select TOP 1 TripsUsers.UserID. I hope this makes sense.

Now I tried the modified sproc you provided.
I get error: The column prefix 'ZipDistances' does not match with a table name or alias name used in the query.
I suspect because you removed the JOIN to ZipDistances from the first JOIN statements.
I appreciate your help very much.

 

[maswien]

Then try following SP,


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 @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 Trips on Trips.TripID = TripsUsers.TripID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where 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 (SELECT count(*) from TripsUsers (nolock), trips WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 0) or --Needs a driver, but doesn''t have one
((' + cast(@NeedsA as char(1)) + ' = 1) AND (SELECT count(*) from TripsUsers (nolock), trips 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, trips WHERE TripsUsers.TripID = Trips.TripID) = (Trips.MaxPassengers-1) AND (SELECT count(*) from TripsUsers (nolock), trips WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 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, trips 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, trips 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 + @WHERE3 + ' order by Trips.TripDate ASC; '
print @SQL
--EXECUTE(@SQL)

 

[maswien]

Sorry , the typo in last SQL, there should be no @where3
included in the @SQL.

 

[organicg]

This latest SPROC did not return any rows, but I will spend some time working with your suggestions. Thank you.

 

[maswien]

Try following SQL, I choose the parameters for you, it returns 2 rows. If this is the SQL you want, you can put it into the SP.



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 = 2
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 Trips on Trips.TripID = TripsUsers.TripID
inner join ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo
where 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 (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 0) 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 (SELECT count(*) from TripsUsers (nolock) WHERE TripsUsers.TripID = Trips.TripID AND TripsUsers.IsDriver = 1) = 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)

 

[organicg]

Unfortunately, the logic to select ZipDistances.Distance for each trip is still incorrect. The correct Distance is not being associated with each trip. However, your suggested SP is finally returning one row for each trip, which was a problem before.

You seem to have discovered a syntax error on my part, too.
In the six &quot;select count(*) subselects after the first occurrence of &quot;@NeedsA&quot;(at the start of the outer most Where clause), the SP has:
SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID
but should be
Select count(*) from TripsUsers, Trips WHERE TripsUsers.TripID = Trips.TripID
I do not know why this doesn't throw an error when executing the SP. It DOES throw an error when that subquery is executed alone in QA. Even more scary is that after adding Trips table reference in all six places, the SP returns NO rows at all!
I tested the @NeedsA parameter(without Trips table reference), and as expected, it provides unreliable/incorrect results, though I'm not sure if this can be said for certain since the rest of the SP isn't reliable either!
Geez, I don't know what else to do at this point, but hope that Zhavic or Logicalman have some luck with their tests.

 

[maswien]

You should see my last SQL, it doesn't contain trips
in the from clause, that is:

SELECT count(*) from TripsUsers WHERE TripsUsers.TripID = Trips.TripID

which is correct, I mistakenly added table 'trips' before.
The way that this works is because it refer to the table
'trips' in the outer query and that's what we want.

Can you describe the question in one sentence using English? I tried to describe the requirement like following:

output every trips that have at least one user who
lives in the zipcode within a given distance from a
given zipcode.

Is this correct?