Need help of inner join For different table

Abhimanyu Singh

13y1.6kSQL

Hii all,

I am writing store procedure for searching by UserType,EmailId and by First name from tblUser

I written the following code but i am not getting aspected result please check the where clause and also inner join because when i am changing the where clause conditions its giving result but throwing sql msg==>StateName property is not found i did not get it please check both.

ALTER Procedure [dbo].[Usp_SearchUserDetails]

(

@UserId int=NULL,

@UserType nvarchar(50)=NULL,

@FirstName nvarchar(50)=NULL,

@EmailId nvarchar(50)=NULL,

@StateId nvarchar(10)=NULL,

@DistrictId nvarchar(10)=NULL,

@SqlMsg nvarchar(Max)output

)

BEGIN

SET @SqlMsg=''

DECLARE @strQuery nvarchar(max)

BEGIN TRY

SET @strQuery='ROW_NUMBER()Over(Order By U.AddDate Desc) as SerialNo,

U.UserType,

U.EmailId,

U.FirstName,

U.LastName,

U.PhoneNo,

U.MobileNo,

U.[Address],

U.PinCode,

U.Company,

S.StateName,

D.DistrictName,

[Password],

CONVERT(Varchar,U.AddDate,103) as AddDate

from tblUser as U

inner join tblState as S

On U.StateId=S.StateId

Inner Join tblDistrict as D

on U.DistrictId=D.DistrictId

Order by U.AddDate Desc

Where UserType=@UserType And EmailId=@EmailId'

IF(@StateId !='0' And @StateId IS Not Null)

Begin

SET @strQuery= @strQuery +' and U.StateId = '''+ @StateId +''' '

End

IF(@DistrictId != '0' And @DistrictId IS Not Null)

Begin

SET @strQuery=@strQuery + ' and U.DistrictId = ''' +@DistrictId+''' '

End

Begin

SET @strQuery=@strQuery + ' Order By U.UserId Desc'

end

print (@strQuery)

Exec(@strQuery)

END TRY

BEGIN CATCH

SET @SqlMsg=ERROR_MESSAGE()

END CATCH

END

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
run the below query directly in your sql and check the result

SELECT U.EmailId,U.StateId,S.StateName FROM tblUser as U
inner join tblState as S
On U.StateId=S.StateId
Inner Join tblDistrict as D
on U.DistrictId=D.DistrictId

Regards,
Iftikar
Iftikar Hussain13y ago
what is the UserType value you are passing from dropdown list? What is there in your database?

Can you debug and check

Regards,
Iftikar
Abhimanyu Singh13y ago
Thanks hussain ji,

Its working almost with searching keywords Email_Id or with First Name Also but...Check the Where condition below...because While i am selecting UserType in Dropdown list its showing
Empty Grid.."No Record found" this is first keywords after this as i selected EmailId and clicked on Search button Its showing result now.

Select ROW_NUMBER()Over(Order By U.AddDate Desc) as SerialNo,
U.UserType,
U.EmailId,
U.FirstName,
U.LastName,
U.PhoneNo,
U.MobileNo,
U.[Address],
U.PinCode,
U.Company,
U.StateId,
S.StateName,
D.DistrictName,
[Password],
CONVERT(Varchar,U.AddDate,103) as AddDate
from tblUser as U
inner join tblState as S
On U.StateId=S.StateId
Inner Join tblDistrict as D
on U.DistrictId=D.DistrictId
Where U.UserType IS NOT NULL AND U.ActiveFlag=1 And U.UserType=@UserType And U.EmailId=@EmailId And U.FirstName=@FirstName
Abhimanyu Singh13y ago
yes it is there...StateName & StateId I taken in tblState

tblState:

CREATE TABLE [dbo].[tblState](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[StateName] [nvarchar](50) NULL,
[StateShortName] [nvarchar](10) NULL,
[AddedBy] [int] NULL,
[AddDate] [datetime] NULL,
[UpadatedBy] [int] NULL,
[UpdateDate] [datetime] NULL,
[ActiveFlag] [bit] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblState] ADD CONSTRAINT [DF_state_AddDate] DEFAULT (getdate()) FOR [AddDate]
GO

ALTER TABLE [dbo].[tblState] ADD CONSTRAINT [DF_state_ActiveFlag] DEFAULT ((1)) FOR [ActiveFlag]
GO

tblUser:

CREATE TABLE [dbo].[tblUser](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserType] [nvarchar](50) NULL,
[EmailId] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[PhoneNo] [nvarchar](50) NULL,
[MobileNo] [nvarchar](50) NULL,
[Address] [nvarchar](500) NULL,
[PinCode] [nvarchar](6) NULL,
[Company] [nvarchar](50) NULL,
[StateId] [int] NULL,
[DistrictId] [int] NULL,
[Password] [nvarchar](50) NULL,
[AddDate] [datetime] NULL,
[UpdateDate] [datetime] NULL,
[ActiveFlag] [bit] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblUser] ADD CONSTRAINT [DF_tblUser_AddDate] DEFAULT (getdate()) FOR [AddDate]
GO

ALTER TABLE [dbo].[tblUser] ADD CONSTRAINT [DF_tblUser_ActiveFlag] DEFAULT ((1)) FOR [ActiveFlag]
GO

Rest thing(Search procedure) mentioned in asked question above....
Iftikar Hussain13y ago
Hi,
Is StateName is exist in tblState? Can you check?

If possible provide all there table schema.

Regards,
Iftikar
Abhimanyu Singh13y ago
Thank you for reply.

No, Actually Before applying of inner join code it was searching/Fetching some but it was showing Sql query msg==> StateName Property is not found so, then applied the above inner join code with help of goooled....but now its not working showing record not found please check the where condition because it wsa same befor apply the inner join code..may be it need to change...and i dont have concept of join..Please Help
13y ago
Are you getting duplicates?
I hope the districts are associated with state table.

from tblUser as U
inner join tblState as S
On U.StateId=S.StateId
Inner Join tblDistrict as D
on U.DistrictId=D.DistrictId
and S.StateId = D.StateId