CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
user194076
user194076

Reputation: 9027

How to avoid coalesce for duplicate tables

I have the following database structure:

tblShelter
ShelterId
PetId
ClientId
ShelterName
Address

tblClient
ClientId
PetId

tblPet
PetId
PetName

So, Pet can belong to a shelter or to a registered shelter client (adopted pet).

So my Shelter table has data as follows: 

ShelterId  PetId ClientId ShelterName 
    1       100    NULL       Test
    1       NULL   101        Test   
    1       102    NULL       Test

So, I need to get all PetNames for each shelter (Direct shelter pets or clients)

Here's my query: 

Select Coalesce(p.PetName, pclient.PetName) as PetName 
from tblShelter s
Left Join tblPet p
on p.PetId = s.PetId   
Left Join (select p2.PetId, p2.PetName 
           from tblClient c 
           join tblPet p2 
           on c.PetId = p2.PetId) pclient 
on s.PetId = pclient.PetId
where shelterId=1

Question: Is it possible to optimize this query? Currently, it doesn't look pretty. Please note that database structure is shown for simplicity. Unfortunately, it cannot be modified. Is it possible to get rid of coalesce in a smart way or get rid of sub query?

Upvotes: 0

Views: 155

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Yes - change your JOIN order:

Select p.PetName 
from tblShelter s
Left Join tblClient c
    on s.ClientId = c.ClientId
inner join tblPet p
    on p.PetId = s.PetId or
       p.PetId = c.PetId
where shelterId=1

JOINs aren't strictly based on joining two tables - the ON clauses can reference any table that has already been joined to form either side of the current join. So here, we allow the join to tblPet to be based on either the direct reference (via s.PetId) or via the optionally joined in tblClient.

Of course, you can further simplify the above query by making a smarter ON condition for the join to tblPet - but the way to do that would be to reintroduce the feature you're seeking to avoid - COALESCE. I've no idea what your reasons are for seeking to avoid that feature though.

Upvotes: 1

Related Questions