Looking for an idea how to construct this T-SQL Select Statement

Question

Post reply

Looking for an idea how to construct this T-SQL Select Statement

VoldemarG

Hall of Fame

Points: 3883
More actions
August 12, 2018 at 3:10 am

#365706

I have 3 Joined tables in a following select statement:
Select A.AgentID, B.AgentSupervisorID, HADDR.AgentAddress
From TableA A Join TableB B on <...> Join TableHomeAddress HADDR on <...>
/* (3 inner joined tables on PK-FK fields) */
There are 2 more tables in the picture (not a part of the join yet, I am looking for a solution on how to join them and how to construct the correct SQL)
   TableD D that has 2 columns : :D.AgentSupervisorID and D.Notes
and TableOfficeAddress OADDR that has 2 columns: OADDR.AgentSupervisorID and OADDR.AgentAddress.
THE PROBLEM:
I need to change the query to satisfy the following condition:
IF  B.AgentSupervisorID value is present in  TableD THEN select AgentAddress from table HADDR (as in the original query above joining 3 tables...)
IF  B.AgentSupervisorID value is NOT present in TableD THEN select AgentAddress from OADDR table.
The only way I can think of doing it is via a cumbersome cursor that would check the condition on row by row basis, saving results into a temp table from which Select at the end of the query or a bulky dynamic SQL.....
Is there a better way to write such query?
Thank you so much for your help. I spent hours trying to figure it out and am still confused...
Likes to play Chess

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

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

andycadley SSCertifiable Points: 5296 More actions · Answer 1

Join OADDR and left join TableD and then do:

IIF (TableD.AgentSupervisorID Is Null, OADDR.AgentAddress, HADDR.AgentAddress) As AgentAddress

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

VoldemarG

Hall of Fame

Points: 3883

August 12, 2018 at 8:56 pm

#2001418

thank you. that worked!

Likes to play Chess

drew.allen SSC Guru Points: 76997 More actions · Answer 3

andycadley - Sunday, August 12, 2018 5:18 AM
Join OADDR and left join TableD and then do:
IIF (TableD.AgentSupervisorID Is Null, OADDR.AgentAddress, HADDR.AgentAddress) As AgentAddress

I see no reason to use proprietary pastiches when there is a perfectly good standard construct.

CASE WHEN TableD.AgentSupervisorID IS NULL THEN OADDR.AgentAddress ELSE HADDR.AgentAddress END AS AgentAddress

CASE is also more flexible in that you can have multiple test conditions.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

drew.allen - Monday, August 13, 2018 8:27 AM
andycadley - Sunday, August 12, 2018 5:18 AM
Join OADDR and left join TableD and then do:
IIF (TableD.AgentSupervisorID Is Null, OADDR.AgentAddress, HADDR.AgentAddress) As AgentAddress
I see no reason to use proprietary pastiches when there is a perfectly good standard construct.
CASE WHEN TableD.AgentSupervisorID IS NULL THEN OADDR.AgentAddress ELSE HADDR.AgentAddress END AS AgentAddress
CASE is also more flexible in that you can have multiple test conditions.
Drew

Not to mention that IIF resolves to a CASE expression in the execution plan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andycadley SSCertifiable Points: 5296 More actions · Answer 5

I prefer IIF for a true/false check because:
a) It's less typing.
b) You can't accidentally miss off the ELSE

YMMV. Ultimately IIF is just syntactic sugar for CASE (see also CHOOSE).

drew.allen SSC Guru Points: 76997 More actions · Answer 6

andycadley - Monday, August 13, 2018 11:43 AM
I prefer IIF for a true/false check because:
a) It's less typing.
b) You can't accidentally miss off the ELSE
YMMV. Ultimately IIF is just syntactic sugar for CASE (see also CHOOSE).

I prefer CASE, because I don't have to remember two different syntaxes, decide which syntax I want to use in a particular situation, switch to a different syntax if the requirements change, and remember if it's a singe I IF or a double I IIF. Besides, I type pretty quickly, and it would probably take me longer to choose between the two options--if I were so inclined--than it would to just type the few extra characters.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

andycadley - Monday, August 13, 2018 11:43 AM
I prefer IIF for a true/false check because:
a) It's less typing.
b) You can't accidentally miss off the ELSE
YMMV. Ultimately IIF is just syntactic sugar for CASE (see also CHOOSE).

I agree with you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101121 More actions · Answer 8

drew.allen - Monday, August 13, 2018 8:27 AM
andycadley - Sunday, August 12, 2018 5:18 AM
Join OADDR and left join TableD and then do:
IIF (TableD.AgentSupervisorID Is Null, OADDR.AgentAddress, HADDR.AgentAddress) As AgentAddress
I see no reason to use proprietary pastiches when there is a perfectly good standard construct.
CASE WHEN TableD.AgentSupervisorID IS NULL THEN OADDR.AgentAddress ELSE HADDR.AgentAddress END AS AgentAddress
CASE is also more flexible in that you can have multiple test conditions.
Drew

Yes, IIF is a truly alien form in T-SQL. If MS keeps up this bastardization of T-SQL, it'll soon be a true Frankenstein language like PL/SQL, :shudder:.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".