Thursday, March 29, 2012

CASE statement within join

Is it possible to have a join with case statement in it?
i.e.
select * from a inner join b on
case [x] then a.xid = b.xid
case [y] then a.yid = b.yid?What condition are you checking for?

That's a pretty malformed CASE statement you've got there...|||Wanted to do a join based on case statements. Instead I will just use the case statements in a function and join based on function.

This is probably not clear, but basically to join based on case statements (i.e. join two tables based on CASE of a field) use:

1. left outer join for each CASE, thus not using CASE in syntax, but using left outer join in place of CASE

2. create a function that checks the field in CASE and returns value. Then join based on function i.e. dbo.wholeword(a.searchword) = b.searchword|||BUT WHAT CONDITION DO YOU WANT TO CHECK FOR?

Is this not a clear request?

My best guess is that you want 2 queries and a union

Good luck

No comments:

Post a Comment