Selecting Query

[suganya]

DoctorMaster

DRID nvarchar(50)
FirstName nvarchar(100)
LastName nvarchar(100)
Gender bit
Address1 nvarchar(255)
Address2 nvarchar(255)
City nvarchar(100)
Pincode numeric(6,0)
State nvarchar(100)
Country nvarchar(100)
Telephone nvarchar(50)
Mobile nvarchar(50)
EmailID nvarchar(100)
Designation nvarchar(50)
Suffix nvarchar(100)
Language nvarchar(10)
SpecialityId nvarchar(100)

Speciality

SPID nvarchar(12)
Speciality vnarchar(50)

DrClinicDetails

DRID nvarchar(12)
CDID nvarchar(12)
HPID nvarchar(12)
HospitalName nvarchar(50)
Address1 nvarchar(255)
Address2 nvarchar(255)
VisitOn char(1)
weekly nvarchar(15)
monthly datetime
status char(1)


I have

Name_TextBox
Specialty_DropD ownList
Location_DropDo wnList

in my page. According to the data in above 3 controls I have to select the value from DB by using the above 3 tables. The selected should be like below

Name: (by appending FirstName,LastN ame & MiddleName from DoctorMaster)
Gender: From DoctorMaster
Address1: From DrClinicDetails
Phone: (by appending Telephone,Mobil e from DoctorMaster)
Suffix: From DoctorMaster

where

FirstName(from DoctorMaster table)=Name_Tex tBox
Specialty(from specialty table)=Specialt y_DropDownList
Country(from DoctorMaster)=L ocation_DropDow nList

The query I have given in .Net is

Select DM.FirstName||D M.MiddleName||D M.LastName "Doctors Name",DM.Gender ,DC.Address1,DM .Telephone||DM. Mobile "Phone",DM.Suff ix from DoctorMaster DM,DrClinicDeta ils DC,Specialty SP where DM.FirstName='a aa' or SP.Specialty='0 01' or DM.Country='Ind ia' and SP.SPID='001'

Its not working.

[deepuv04]

Hi,
Try the query in the following way...

[code=sql]
Select DM.FirstName + '||' + DM.MiddleName + '||' + DM.LastName AS Doctors Name,
DM.Gender,DC.Ad dress1,DM.Telep hone + '||' + DM.Mobile AS Phone,
DM.Suffix
from DoctorMaster DM INNER JOIN
DrClinicDetails DC DM.DRID = DC.DRID INNER JOIN
Specialty SP SP.SPID = DM.SpecialityId
where ( DM.FirstName='a aa' or SP.Specialty='0 01' or DM.Country='Ind ia') and SP.SPID='001'

[/code]
thanks

[suganya]

Hi

If I run the following query in sql server 2005, its displaying the data.

Select DM.FirstName+' '+DM.MiddleName +' '+DM.LastName "DoctorsName",D M.Gender,DC.Add ress,DM.Telepho ne+DM.Mobile "Phone",PT.Prim aryTitle from DoctorMaster DM,DrClinicDeta ils DC,Specialty SP,PrimaryTitle PT where DM.FirstName='a aa' or SP.Specialty='a aa' or DM.City='madura i' and SP.SPID=DM.SPID and PT.PTID=DM.Prim aryTitleID

But if I give the same query as

string sql="Select DM.FirstName+'' +DM.MiddleName+ ''+DM.LastName "DoctorsName",D M.Gender,DC.Add ress,DM.Telepho ne+''+DM.Mobile "Phone",PT.Prim aryTitle from DoctorMaster DM,DrClinicDeta ils DC,Specialty SP,PrimaryTitle PT where DM.FirstName='" + obj.ToString() + "' or SP.Specialty='" + obj1.ToString() + "' or DM.City='" + obj2.ToString() + "' and SP.SPID=DM.SPID and PT.PTID=DM.Prim aryTitleID";

in asp.net then it is showing the error as

; expected

[suganya]

Than U Deepuv04. Its not showing any error but still I have a doubt, in the above query if I add another table

PrimaryTitle

PTID nvarchar(12)
PrimaryTitle nvarchar(20)

and give the query like

Select DM.FirstName + '' + DM.MiddleName + '' + DM.LastName AS DoctorsName, DM.Gender,DC.Ad dress,DM.Teleph one + '' + DM.Mobile AS Phone,PT.Primar yTitle from DoctorMaster DM INNER JOIN DrClinicDetails DC DM.DRID = DC.DRID INNER JOIN Specialty SP SP.SPID = DM.SPID INNER JOIN PrimaryTitle PT PT.PTID=DM.Prim aryTitleID where ( DM.FirstName like 'a' or SP.Specialty='a aa' or DM.City='madura i')


It is showing the following error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DM'.

[deepuv04]

Hi,
coming to your first error, since you are building the query as a string in .net you need to follow the .net syntax to build the string. like when you are moved to next line you need to add " & _" at the end of previous.

the code will be:
[code=sql]

string sql="Select DM.FirstName+'' +DM.MiddleName+ ''+DM.LastName ""DoctorsName"" ," & _
"DM.Gender,DC.A ddress,DM.Telep hone+''+DM.Mobi le ""Phone"",PT.Pr imaryTitle " & _
"from DoctorMaster DM,DrClinicDeta ils DC,Specialty SP,PrimaryTitle PT " & _
"where (DM.FirstName=' " + obj.ToString() + "' or SP.Specialty='" + obj1.ToString() + "' or DM.City='" + obj2.ToString() + "')" & _
" and SP.SPID=DM.SPID and PT.PTID=DM.Prim aryTitleID";
[/code]

about second error: you missed the key word "ON" in the joining syntax.
the code is:
[code =sql]
Select DM.FirstName + '' + DM.MiddleName + '' + DM.LastName AS DoctorsName,
DM.Gender,DC.Ad dress,DM.Teleph one + '' + DM.Mobile AS Phone,PT.Primar yTitle
from DoctorMaster DM INNER JOIN
DrClinicDetails DC on DM.DRID = DC.DRID INNER JOIN
Specialty SP on SP.SPID = DM.SPID INNER JOIN
PrimaryTitle on PT PT.PTID=DM.Prim aryTitleID
where ( DM.FirstName like 'a' or SP.Specialty='a aa' or DM.City='madura i')
[/code]