Conditional Inner Join problem

Question

Post reply

Conditional Inner Join problem

John Reynolds-331193

SSC Enthusiast

Points: 104
More actions
June 5, 2008 at 10:26 am

#183173

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!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

John Reynolds-331193 SSC Enthusiast Points: 104 More actions · Answer 5

Thanks guys. Exactly what I was looking for, and of course more simple than what I was trying... :blush:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Let us know which way you go. I am curious which works better for you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

Conditional Inner Join problem

Cookies on SQLServerCentral