Need Select Help Please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Select Help Please.

Author

Topic

MartyBevo
Starting Member

10 Posts

Posted - 2007-07-10 : 09:07:51

I'm a novice at SQL and may have bitten off more than I can chew on this one. Any help is greatly appreciated.

I am trying to perform a search and am unable to get any results. My Primary Select (see below) works just fine. When I'm directed to my web page I receive a list of consultants. At the top of the form I added a drop-down list which is pulling States from a LIsTS table... which also appears to function. My problem is when I select a State from the drop-down and select GO... nothing works.

Can anybody help to get my filter working please? Thanks.

TEXT SELECT (State Drop-Down box):

SELECT ListName, Value, Text, ParentID FROM Lists WHERE ParentID='221' ORDER BY Text

PRIMARY SELECT (Consultant Listing):

SELECT

'' +
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),1,1) + SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),2,25) + ', ' + SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),1,1) + SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),2,25) + ' ' +
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) + ', ' +
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) + ' ' +
MAX(CASE WHEN propertyName = 'PostalCode' THEN propertyValue END) AS Address,
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) AS Test,

'Home: ' +
MAX(CASE WHEN propertyName = 'Telephone' THEN propertyValue END) + ' ' +

'Cell: ' +
MAX(CASE WHEN propertyName = 'Cell' THEN propertyValue END) + ' ' +

'Website: ' +
'<a href=http://cookseykeepsakes.martybevo.com/'+Username+'>http://cookseykeepsakes.martybevo.com/' + Username + '</a>' + ' ' +

'Email: ' +
Email AS Contact

FROM

(SELECT DISTINCT PropertyName, PropertyValue, UserID FROM ProfilePropertyDefinition AS ppd INNER JOIN UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d

INNER JOIN UserRoles ON d.UserID = UserRoles.UserID
INNER JOIN Users ON UserRoles.UserID = Users.UserID
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID

WHERE

(Roles.PortalID = 15 AND Roles.RoleName = 'Subscribers')

GROUP BY

d.UserID, UserRoles.RoleID, Users.Email, Users.Username, Roles.RoleName

ORDER BY

UserRoles.RoleID

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-10 : 09:36:09

Whoa...let's step back, ok

Can you give us some DDL, sample data and expected results?

Read the hin link in my sig below and post what it asks for

You should get an answer in minutes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

MartyBevo
Starting Member

10 Posts

Posted - 2007-07-11 : 21:58:58

OK, I wasn't sure how to lay this out but I took your hint and did the best that I can. Hopefully, this will provide you with the info needed. If not, please let me know... and thank you all for your support.

1. State the question.

I want to be able to filter my data based on the value selected from my drop-down list.

2.DDL of table.

/****** Object: Table [dbo].[ProfilePropertyDefinition] Script Date: 7/11/2007 9:30:41 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProfilePropertyDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProfilePropertyDefinition]
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProfilePropertyDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ProfilePropertyDefinition] (
[PropertyDefinitionID] [int] IDENTITY (1, 1) NOT NULL ,
[PortalID] [int] NULL ,
[ModuleDefID] [int] NULL ,
[Deleted] [bit] NOT NULL ,
[DataType] [int] NOT NULL ,
[DefaultValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Length] [int] NOT NULL CONSTRAINT [DF_ProfilePropertyDefinition_Length] DEFAULT ((0)),
[Required] [bit] NOT NULL ,
[ValidationExpression] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ViewOrder] [int] NOT NULL ,
[Visible] [bit] NOT NULL ,
CONSTRAINT [PK_ProfilePropertyDefinition] PRIMARY KEY CLUSTERED
(
[PropertyDefinitionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProfilePropertyDefinition_Portals] FOREIGN KEY
(
[PortalID]
) REFERENCES [dbo].[Portals] (
[PortalID]
) ON DELETE CASCADE
) ON [PRIMARY]
CREATE UNIQUE INDEX [IX_ProfilePropertyDefinition] ON [dbo].[ProfilePropertyDefinition]([PortalID], [ModuleDefID], [PropertyName]) ON [PRIMARY]
CREATE INDEX [IX_ProfilePropertyDefinition_PropertyName] ON [dbo].[ProfilePropertyDefinition]([PropertyName]) ON [PRIMARY]
END

GO

3. DML Sample. Not sure what that is.

4.DML Attempted.

SELECT

'' +
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),2,25) + ', ' +
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),2,25) + ' ' +
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) + ', ' +
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) + ' ' +
MAX(CASE WHEN propertyName = 'PostalCode' THEN propertyValue END) AS Address,
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) AS Test,

'Home: ' +
MAX(CASE WHEN propertyName = 'Telephone' THEN propertyValue END) + ' ' +
'Cell: ' +
MAX(CASE WHEN propertyName = 'Cell' THEN propertyValue END) + ' ' +
'Website: ' +
'<a href=http://cookseykeepsakes.martybevo.com/'+Username+'>http://cookseykeepsakes.martybevo.com/' + Username + '</a>' + ' ' +
'Email: ' +
Email AS Contact

FROM

(SELECT DISTINCT PropertyName, PropertyValue, UserID FROM ProfilePropertyDefinition AS ppd
INNER JOIN UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d
.
INNER JOIN UserRoles ON d.UserID = UserRoles.UserID
INNER JOIN Users ON UserRoles.UserID = Users.UserID
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID

WHERE

(Roles.PortalID = 15 AND Roles.RoleName = 'Subscribers')

GROUP BY d.UserID, UserRoles.RoleID, Users.Email, Users.Username, Roles.RoleName

ORDER BY UserRoles.RoleID

5. Expected Results

When the webpage opens the Select statement from step 4 lists all consultants in the United States. At the top of the page a State drop-down list is available for the user to select a state and filter his selection. For example, a list of 100 consultants are listed. I select Pennsylvania from the drop-down list and select the GO button and then 5 consultants list that are from Pennsylvania.

MartyBevo
Starting Member

10 Posts

Posted - 2007-07-15 : 21:41:11

Can anyone assist me on this issue? Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources