How can I use CASE in a INNER JOIN

I need help with the following logic:

I have two tables

  1. inv_corp
  2. hst_ref

When inv_corp.btlr_dlvr_pnt_no = hst_ref.old_dlvr_pnt_no Then select hst_ref.new_dlvr_pnt_no Else inv_corp.btlr_dlvr_pnt_no

I feel i could easily do this in the SELECT statement had there been a static field I could join to of the table. But being that the joining of these two tables is dependent on a criteria first it make this a little trickier for me.

Any help would be much appreciated

How big are the tables

Please provide:

  • table definitions in the form og create statements
  • sample data in the form og insert statements
  • expected output from the sample data you provide
1 Like

If I understand correctly, then something like this:

SELECT ..., ISNULL(hr.new_dlvr_pnt_no, ic.btlr_dlvr_pnt_no) AS hst_ref_lookup, ...
FROM inv_corp ic
LEFT OUTER JOIN hst_ref hr ON hr.old_dlvr_pnt_no = ic.btlr_dlvr_pnt_no

maybe this?

create table inv_corp(btlr_dlvr_pnt_no int);
insert into inv_corp
select top 10 object_id  from sys.objects

create table hst_ref(old_dlvr_pnt_no int, new_dlvr_pnt_no int)
insert into hst_ref
select top 10 object_id, object_id * 2  from sys.objects

select *,
	     when i.btlr_dlvr_pnt_no = h.old_dlvr_pnt_no then h.new_dlvr_pnt_no
		 else i.btlr_dlvr_pnt_no
	   end as boomBam
  from inv_corp i, hst_ref h

How would I join i and h table though? I don't have a particular column to just do the JOIN on. The join is determined by rather the btlr_dlvr_pnt_no from table "i" equals table's "h" column or not

Did you try out the recommended solutions ?

You're not at all clear on what you want to do, but this is my best guess for now. If you're trying to match on one, and if no match, then match on the other, a CASE statement can't easily do that for you.

SELECT ..., ISNULL(hr1.new_dlvr_pnt_no, h2.new_dlvr_pnt_no) AS hst_ref_lookup, ...
FROM inv_corp ic
LEFT OUTER JOIN hst_ref hr1 ON hr1.old_dlvr_pnt_no = ic.btlr_dlvr_pnt_no
LEFT OUTER JOIN hst_ref hr2 ON hr1.old_dlvr_pnt_no IS NULL AND hr1.new_dlvr_pnt_no = ic.btlr_dlvr_pnt_no

may be dynamic SQL ...

dynamic sql example ..
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = @city'

EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city