June 5, 2008 at 10:26 am
I have two tables that I would like to join. The first table 'Provider' has the following fields:
ProviderID, ProviderName
The second table, 'Service' has the following fields:
ServiceID, StructureID, PhysicianID
In the table 'Service' either StructureID will have a value and PhysicianID will be NULL or PhysicianID will have a value and StructureID will be NULL. Which ever value is not NULL I want to Join on ProviderID to get the ProviderName.
Can someone help point me in the right direction for writing this querry?
Thanks!
June 5, 2008 at 10:35 am
Hope this helps:
create table dbo.Provider (
ProviderID int,
ProviderName varchar(25)
)
create table dbo.Service (
ServiceID int,
StructureID int,
PhysicianID int)
insert into dbo.Provider values (1,'John Doe')
insert into dbo.Service values (1,null,1)
insert into dbo.Service values (2,1,null)
select * from dbo.Provider
select * from dbo.Service
select
*
from
dbo.Provider
inner join dbo.Service
on (Service.StructureID is null and Service.PhysicianID = Provider.ProviderID
or Service.PhysicianID is null and Service.StructureID = Provider.ProviderID)
drop table dbo.Provider
drop table dbo.Service
😎
June 5, 2008 at 11:15 am
If the two really are exclusive - I can't help but think that a UNION ALL query would be fastest.
Select s.serviceID, s.structureID, s.providerID,p.providerName
from
Service s
inner join provider p on s.providerID=p.providerID
UNION ALL
Select s.serviceID, s.structureID, s.providerID,p.providerName
from
Service s
inner join provider p on s.structureID=p.providerID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 11:30 am
Matt, could be. Best thing would be for the OP to test both and decide from there. With my very minimal test code, who knows.
😎
June 5, 2008 at 11:32 am
Lynn Pettis (6/5/2008)
Matt, could be. Best thing would be for the OP to test both and decide from there. With my very minimal test code, who knows.😎
yup!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 11:37 am
Thanks guys. Exactly what I was looking for, and of course more simple than what I was trying... :blush:
June 5, 2008 at 11:45 am
Let us know which way you go. I am curious which works better for you.
😎
June 5, 2008 at 11:49 am
For clarity in my code, this is how the query should be coded:
select
*
from
dbo.Provider
inner join dbo.Service
on ((Service.StructureID is null and Service.PhysicianID = Provider.ProviderID)
or (Service.PhysicianID is null and Service.StructureID = Provider.ProviderID))
It works the other way due to precedence. The AND clauses happen to be evaluated first, then the OR clause.
😎
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply