CODEWITHSUNDEEP
sqlsql-servert-sql
František Škandera
František Škandera

Reputation: 187

The most effective implementation of conditional join

In T-SQL I have a view, that includes a join:

inner join db..address addr
on addr.town = f.town

I need to somehow modify this join, if f.town is a specific value (let's say "abc"), it also includes another value (ie. "abcd"). In all other cases it only takes the value of f.town. Like this:

inner join db..address addr
on addr.town in ("abc", "abcd")

I tried to do this but it doesn't work:

inner join db..address addr
on (case when f.town = "abc"
         then addr.town = f.town
         else addr.town in (f.town, "abcd"))

Any suggestions?

Upvotes: 1

Views: 69

Answers (2)

twrowsell
twrowsell

Reputation: 467

Would this do the trick?

inner join db.address addr 
on  f.town = addr.town OR (f.town='abc' AND addr.town ="abcd")

Upvotes: 1

MarkD
MarkD

Reputation: 5316

If I understand you, you're trying to create a structure similar to this? Haven't tested it and it's a messy thing - please provide sample data if you can;

SELECT *
FROM TableA A
JOIN TableB B   ON 1 =  (   
                            CASE 
                                WHEN A.Moo = B.Moo THEN 1
                                WHEN A.Moo = B.Moo + 'x' THEN 1
                                ELSE 0
                            END 
                        )

Upvotes: 0

Related Questions