Joins

I am trying to join multiple tables using FROM joins. I want to pick up
only OB/GYN Companies but am instead getting everything.
Here's my SQL...
SELECT COMPANY.[NAME],
COMPANY.[ADDRESS],
COMPANY.[CITY],
COMPANY.[STATE],
COMPANY.[ZIP],
COMPANY.[MAIN_CONTACT],
COMPANY.[MAINCONTCTPHONE],
CMDBSPEC.[CMDB_SPEC_DESC],
CMDBCOMP.[CMDB_COMPANY_PRODUCT_PURCHASE]
FROM [magictsd].[_SMDBA_].[_COMPANY_] COMPANY
INNER JOIN [magictsd].[_SMDBA_].[CMDB_COMPANY] CMDBCOMP
ON COMPANY.SEQUENCE = CMDBCOMP.SEQUENCE
INNER JOIN [magictsd].[_SMDBA_].[CMDB_SPEC] CMDBSPEC
ON CMDBCOMP.CMDB_COMPANY_SEQ_SPECIALTY = CMDBSPEC.SEQUENCE
AND CMDBSPEC.CMDB_SPEC_DESC LIKE '%Ob%'
OR CMDBSPEC.CMDB_SPEC_DESC LIKE '%Gyn%'
Can anyone help me? I thought INNER JOIN was the answer since I want
ONLY the OB/GYN Companies.
Any help is GREATLY appreciated!Without DDLs & sample data this is only a guess: Try changing the OR in your
JOIN clause to an AND.
Anith|||Well the last two Join condiditons should be surrounded by Parentheses, but
actually even better, would be to put them in a Where clause instead of as a
Join Condition...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'|||Sorry, your field names are a bit unusual, I typoed trying t ocopy them...
Here is corrected version...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Main_Contact, C.MainContctPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'
"CBretana" wrote:

> Well the last two Join condiditons should be surrounded by Parentheses, bu
t
> actually even better, would be to put them in a Where clause instead of as
a
> Join Condition...
> Select C.Name, C.Address, C.City, C.State,
> C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
> S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
> From _Company C
> Join Cmdb_Company CC
> On CC.Sequence = C.Sequence
> Join Cmdb_Spec S
> On S.Sequence = CC.Cmdb_Company_Seq_Speciality
> Where S.Cmdb_Spec_Desc Like '%Ob%'
> Or S.Cmdb_Spec_Desc Like '%Gyn%'