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_DropDownList
Location_DropDownList

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,LastName & MiddleName from DoctorMaster)
Gender: From DoctorMaster
Address1: From DrClinicDetails
Phone: (by appending Telephone,Mobile from DoctorMaster)
Suffix: From DoctorMaster

where

FirstName(from DoctorMaster table)=Name_TextBox
Specialty(from specialty table)=Specialty_DropDownList
Country(from DoctorMaster)=Location_DropDownList

The query I have given in .Net is

Select DM.FirstName||DM.MiddleName||DM.LastName "Doctors Name",DM.Gender,DC.Address1,DM.Telephone||DM.Mobil e "Phone",DM.Suffix from DoctorMaster DM,DrClinicDetails DC,Specialty SP where DM.FirstName='aaa' or SP.Specialty='001' or DM.Country='India' and SP.SPID='001'

Its not working.

[deepuv04]

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

Select DM.FirstName + '||' + DM.MiddleName + '||' + DM.LastName AS Doctors Name,
       DM.Gender,DC.Address1,DM.Telephone + '||' + 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='aaa' or SP.Specialty='001' or DM.Country='India') and SP.SPID='001'
 
 

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",DM.Gender,DC.Address,DM.Telephone+DM .Mobile "Phone",PT.PrimaryTitle from DoctorMaster DM,DrClinicDetails DC,Specialty SP,PrimaryTitle PT where DM.FirstName='aaa' or SP.Specialty='aaa' or DM.City='madurai' and SP.SPID=DM.SPID and PT.PTID=DM.PrimaryTitleID

But if I give the same query as

string sql="Select DM.FirstName+''+DM.MiddleName+''+DM.LastName "DoctorsName",DM.Gender,DC.Address,DM.Telephone+'' +DM.Mobile "Phone",PT.PrimaryTitle from DoctorMaster DM,DrClinicDetails 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.PrimaryTitleID";

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.Address,DM.Telephone + '' + DM.Mobile AS Phone,PT.PrimaryTitle 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.PrimaryTitleID where ( DM.FirstName like 'a' or SP.Specialty='aaa' or DM.City='madurai')


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:

 
string sql="Select DM.FirstName+''+DM.MiddleName+''+DM.LastName  ""DoctorsName""," & _
           "DM.Gender,DC.Address,DM.Telephone+''+DM.Mobile ""Phone"",PT.PrimaryTitle " & _
           "from DoctorMaster DM,DrClinicDetails 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.PrimaryTitleID";
 

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.Address,DM.Telephone + '' + DM.Mobile AS Phone,PT.PrimaryTitle
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.PrimaryTitleID
where ( DM.FirstName like 'a' or SP.Specialty='aaa' or DM.City='madurai')
[/code]