Simple select question
-
i am tring to run a simple select and for some reason i am not able to get this to work this morning
My querry works withOUT the WHERE CLAUSE... but i have to have a where clause
can someone please point me in the right direction?
thanks!
erik
============================================================================
SELECT Users.UserID AS UserID, Users.UserType AS UserType, Users.FirstName AS FirstName, Users.LastName AS LastName,
Specialty.SpecialtyName AS SpecialtyName
FROM Users INNER JOIN
UserSpecialty ON Users.UserID = UserSpecialty.UserID INNER JOIN
Specialty ON UserSpecialty.SpecialtyCD = Specialty.SpecialtyCD
WHERE User.UserID = 49 AND Users.Email = 'erik_little@afcc1.com' AND Users.LastName = 'little' AND Users.FirstName = 'erik'
==========================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[is_performed_by]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserSpecialty] DROP CONSTRAINT is_performed_by
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[purchases_items_via]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT purchases_items_via
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[offers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Product] DROP CONSTRAINT offers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[specializes_in]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserSpecialty] DROP CONSTRAINT specializes_in
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Specialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Specialty]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserSpecialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserSpecialty]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO
CREATE TABLE [dbo].[Specialty] (
[SpecialtyCD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SpecialtyName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserSpecialty] (
[UserID] [int] NOT NULL ,
[SpecialtyCD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[OComp] [int] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActiveInd] [int] NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WkPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HmPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyPhoto] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyDescr] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [binary] (24) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Dam again!
-
Here's a good one : Why not just WHERE User.UserID = 49
?? If that doesn't do it for you then check the data, there's surely a type in either the query or the table.
-
The first restriction is "WHERE User.UserID = 49"
But "User" is a reserved word and the table name is "userS"
SQL = Scarcely Qualifies as a Language
-
This is not the actual data that i will use to pass to the columns in the where clause. I will be using @userid
Parameters.
i have tried that and it does not work
sos
Dam again!
-
That was a misstype.. It is Where Users.UserID = @user-id
Dam again!
-
Do you get a syntax error or no rows back?
-
no syntax error... just no rows back.
erik
Dam again!
-
CHECK THE DATA.
-
I often have problems like this, generally when I'm trying to debug another developer's code. I generally start by removing the where clause completely. After that I add back the where conditions one by one to determine which one is eliminating all rows. Most times examining the raw data shows that the data is not what is expected - a typo, a middle initial inserted as part of the first name, etc...
-
Thank you for the reply. This is a classic case of no Data in the fields and so no records were being returned.
This has happened to me about ten times, and i am hoping that i finally learn for this last one.
Thanks!
erik..
Dam again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply