rortiz77
asked on
Query for avoiding multiple rows.
System: SQL Enterprise Manager v 8.0
I'm trying to make a query that will grab all the "board_specialty" from the physicians tables. The problem is in the way the tables are designed. This is how it looks:
Board_Name Specialty_Name Order_
American Board of... Surgery 1
American Board of... Family Practice 2
American Board of... Internal Medicine 3
How I'd like it to look after the query (to avoid 3 rows with the same info):
Physician_Name Board_Name1 Board_Name2 Board_Name3 Spec_Name
John Doe American board of... American board of... American Board of... Surgery
Mary Sue American board of... American board of... American board of... Pediatrics
Bob Dillon American board of... American board of... American board of... Cardiovascular
I dont need to query the multiple secialty names...just the different board names on a flat file.
Thanks!
I'm trying to make a query that will grab all the "board_specialty" from the physicians tables. The problem is in the way the tables are designed. This is how it looks:
Board_Name Specialty_Name Order_
American Board of... Surgery 1
American Board of... Family Practice 2
American Board of... Internal Medicine 3
How I'd like it to look after the query (to avoid 3 rows with the same info):
Physician_Name Board_Name1 Board_Name2 Board_Name3 Spec_Name
John Doe American board of... American board of... American Board of... Surgery
Mary Sue American board of... American board of... American board of... Pediatrics
Bob Dillon American board of... American board of... American board of... Cardiovascular
I dont need to query the multiple secialty names...just the different board names on a flat file.
Thanks!
SELECT B1.Physician_Name, coalesce(B1.Board_Name,'') as Board_Name1, coalesce(B2.Board_Name,'') as Board_Name2,
coalesce(B3.Board_Name,'') as Board_Name3, B1.Specialy_Name as Spec_Name
From Board_Specialty B1
Left outer Join Board_Specialty B2
on B1.Physician_Name = B2.Phsyician_Name
And B2.Order = 2
Left Outer Join Board_Specialty B3
on B1.Physician_Name = B3.Phsyician_Name
And B3.Order = 3
Where B1.Order = 1
coalesce(B3.Board_Name,'')
From Board_Specialty B1
Left outer Join Board_Specialty B2
on B1.Physician_Name = B2.Phsyician_Name
And B2.Order = 2
Left Outer Join Board_Specialty B3
on B1.Physician_Name = B3.Phsyician_Name
And B3.Order = 3
Where B1.Order = 1
ASKER
True, there are more details. I over simplified the query. The query right now is made up of 4 tables.
Med_Prof_Spec:
-Board_name
Med_Prof:
-Last_name
-First_name
-Middle_initial
-Degree
-Languages_spoken
Med_Prof_Offices:
-Address
-City
-Zip
-Phone
Med_Prof_Facilities:
-Expertise
Here is the current query:
SELECT
dbo.Med_Prof.Last_name,
dbo.Med_Prof.First_name,
dbo.Med_Prof.Middle_initia l,
dbo.Med_Prof.Degree,
dbo.Med_Prof_Offices.Phone _number_1,
dbo.Med_Prof_Offices.Fax_n umber,
dbo.Med_Prof_Facilities.Ex pertise,
dbo.Med_Prof_Offices.Addre ss_1,
dbo.Med_Prof_Offices.Addre ss_2,
dbo.Med_Prof_Offices.City,
dbo.Med_Prof_Offices.State ,
dbo.Med_Prof_Offices.Zip_C ode,
dbo.Med_Prof.Languages_spo ken,
dbo.Med_Prof_Specialties.B oard_name
FROM dbo.Med_Prof INNER JOIN
dbo.Med_Prof_Offices ON dbo.Med_Prof.Med_Prof_Reco rd_No =
dbo.Med_Prof_Offices.Med_P rof_Record _No INNER JOIN
dbo.Med_Prof_Facilities ON dbo.Med_Prof.Med_Prof_Reco rd_No =
dbo.Med_Prof_Facilities.Me d_Prof_Rec ord_No INNER JOIN
dbo.Med_Prof_Specialties ON dbo.Med_Prof.Med_Prof_Reco rd_No =
dbo.Med_Prof_Specialties.M ed_Prof_Re cord_No AND
dbo.Med_Prof_Facilities.Or der_ =
dbo.Med_Prof_Specialties.O rder_ AND dbo.Med_Prof_Offices.Order _ =
dbo.Med_Prof_Specialties.O rder_
WHERE
(dbo.Med_Prof_Offices.Prim ary_office = 1) AND
(dbo.Med_Prof_Facilities.C urrent_sta tus = 'fellow' OR
dbo.Med_Prof_Facilities.Cu rrent_stat us = 'active'
(dbo.Med_Prof_Facilities.F acCode = 'fhorland01') AND
(dbo.Med_Prof.Degree = 'md' OR
dbo.Med_Prof.Degree = 'DDS MD' OR
dbo.Med_Prof.Degree = 'dds, md' OR
dbo.Med_Prof.Degree = 'md, dds'
ORDER BY
dbo.Med_Prof.Last_name,
dbo.Med_Prof.First_name
Med_Prof_Spec:
-Board_name
Med_Prof:
-Last_name
-First_name
-Middle_initial
-Degree
-Languages_spoken
Med_Prof_Offices:
-Address
-City
-Zip
-Phone
Med_Prof_Facilities:
-Expertise
Here is the current query:
SELECT
dbo.Med_Prof.Last_name,
dbo.Med_Prof.First_name,
dbo.Med_Prof.Middle_initia
dbo.Med_Prof.Degree,
dbo.Med_Prof_Offices.Phone
dbo.Med_Prof_Offices.Fax_n
dbo.Med_Prof_Facilities.Ex
dbo.Med_Prof_Offices.Addre
dbo.Med_Prof_Offices.Addre
dbo.Med_Prof_Offices.City,
dbo.Med_Prof_Offices.State
dbo.Med_Prof_Offices.Zip_C
dbo.Med_Prof.Languages_spo
dbo.Med_Prof_Specialties.B
FROM dbo.Med_Prof INNER JOIN
dbo.Med_Prof_Offices ON dbo.Med_Prof.Med_Prof_Reco
dbo.Med_Prof_Offices.Med_P
dbo.Med_Prof_Facilities ON dbo.Med_Prof.Med_Prof_Reco
dbo.Med_Prof_Facilities.Me
dbo.Med_Prof_Specialties ON dbo.Med_Prof.Med_Prof_Reco
dbo.Med_Prof_Specialties.M
dbo.Med_Prof_Facilities.Or
dbo.Med_Prof_Specialties.O
dbo.Med_Prof_Specialties.O
WHERE
(dbo.Med_Prof_Offices.Prim
(dbo.Med_Prof_Facilities.C
dbo.Med_Prof_Facilities.Cu
(dbo.Med_Prof_Facilities.F
(dbo.Med_Prof.Degree = 'md' OR
dbo.Med_Prof.Degree = 'DDS MD' OR
dbo.Med_Prof.Degree = 'dds, md' OR
dbo.Med_Prof.Degree = 'md, dds'
ORDER BY
dbo.Med_Prof.Last_name,
dbo.Med_Prof.First_name
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Server: Msg 156, Level 15, State 1, Procedure vwMedProfBoard, Line 31
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Procedure vwMedProfBoard, Line 56
Incorrect syntax near the keyword 'order'.
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Procedure vwMedProfBoard, Line 56
Incorrect syntax near the keyword 'order'.
ASKER
What is causing this error?
LowFatSpread's answer is a view, then a query of that view. If you want to execute the creation of the view at the same time as the query, replace that middle THEN with GO.
(btw, the solution's his, I'm just helping out a bit.)
The mod I suggest above makes it look like this:
Create View vwMedProfBoard
as
Select MPO.Med_prof_record_no ,
MPO.Phone_number_1,
MPO.Fax_number,
MPF.Expertise,
MPO.Address_1,
MPO.Address_2,
MPO.City,
MPO.State,
MPO.Zip_Code,
MP.Languages_spoken,
MPS.Board_name,mps_order
From dbo.Med_Prof_Offices as MPO
INNER JOIN dbo.Med_Prof_Facilities as MPF
ON MP.Med_Prof_Record_No = MPF.Med_Prof_Record_No
INNER JOIN dbo.Med_Prof_Specialties as MPS
ON MP.Med_Prof_Record_No = MPS.Med_Prof_Record_No
AND MPF.Order_ = MPS.Order_
AND MPO.Order_ = MPS.Order_
WHERE MPO.Primary_office = 1
AND MPF.Current_status in ('fellow','active')
AND MPF.FacCode = 'fhorland01'
AND MP.Degree in ('md','DDS MD' ,'dds, md' , 'md, dds' )
GO --here's the change
SELECT
MP.Last_name,
MP.First_name,
MP.Middle_initial,
MP.Degree,
B1.Phone_number_1, b1.Fax_number, b1.Expertise, b1.Address_1, b1.Address_2, b1.City,
b1.State, b1.Zip_Code, b1.Languages_spoken,
b1.Board_name,
b2.Board_name,
b3.Board_name
FROM dbo.Med_Prof as MP
Inner Join vwMedProfBoard as B1
on mp.med_prof_record_no=b1.m ed_prof_re cord_no
left outer join vwMedProfBoard as B2
on mp.med_prof_record_no=b2.m ed_prof_re cord_no
and b2.mps_order > b1.order
left outer join vwMedProfBoard as B3
on mp.med_prof_record_no=b3.m ed_prof_re cord_no
and b3.mps_order > b2.order
ORDER BY MP.Last_name, MP.First_name
(btw, the solution's his, I'm just helping out a bit.)
The mod I suggest above makes it look like this:
Create View vwMedProfBoard
as
Select MPO.Med_prof_record_no ,
MPO.Phone_number_1,
MPO.Fax_number,
MPF.Expertise,
MPO.Address_1,
MPO.Address_2,
MPO.City,
MPO.State,
MPO.Zip_Code,
MP.Languages_spoken,
MPS.Board_name,mps_order
From dbo.Med_Prof_Offices as MPO
INNER JOIN dbo.Med_Prof_Facilities as MPF
ON MP.Med_Prof_Record_No = MPF.Med_Prof_Record_No
INNER JOIN dbo.Med_Prof_Specialties as MPS
ON MP.Med_Prof_Record_No = MPS.Med_Prof_Record_No
AND MPF.Order_ = MPS.Order_
AND MPO.Order_ = MPS.Order_
WHERE MPO.Primary_office = 1
AND MPF.Current_status in ('fellow','active')
AND MPF.FacCode = 'fhorland01'
AND MP.Degree in ('md','DDS MD' ,'dds, md' , 'md, dds' )
GO --here's the change
SELECT
MP.Last_name,
MP.First_name,
MP.Middle_initial,
MP.Degree,
B1.Phone_number_1, b1.Fax_number, b1.Expertise, b1.Address_1, b1.Address_2, b1.City,
b1.State, b1.Zip_Code, b1.Languages_spoken,
b1.Board_name,
b2.Board_name,
b3.Board_name
FROM dbo.Med_Prof as MP
Inner Join vwMedProfBoard as B1
on mp.med_prof_record_no=b1.m
left outer join vwMedProfBoard as B2
on mp.med_prof_record_no=b2.m
and b2.mps_order > b1.order
left outer join vwMedProfBoard as B3
on mp.med_prof_record_no=b3.m
and b3.mps_order > b2.order
ORDER BY MP.Last_name, MP.First_name
If you have a MAXIMUM number of boards on which a physician may be, you can do something like this:
SELECT B1.Physician_Name, B1.Board_Name as Board_Name1, B2.Board_Name as Board_Name2,
... Bn.Board_Name as Board_NameN, B1.Specialy_Name as Spec_Name
From
Board_Specialty B1 Left Join
Board_Specialty B2 on B1.Physician_Name = B2.Phsyician_Name And B1.Order_ = 1 And IsNull(B2.Order,2) = 2
... Left Join Board_Specialty Bn on B1.Physician_Name = Bn.Phsyician_Name And IsNull(Bn.Order,n) = n
Honestly, I think your table structure is a bit more complex than that. If this query doesn't get you there, please tell us about the related tables.