CODEWITHSUNDEEP
sqljoin
user1864446
user1864446

Reputation: 31

Join SQL tables by field values

I need to join two tables to get the Reason_Descriptions for multiple fields from another table based on the Reason_Id. My problem is that I am not sure how to compare the field values.

Table 1:
  Reason_Id,
  Reason_Description


Table 2:
  Reason1_Id,
  Reason2_Id,
  Reason3_Id

The values in the Table 2 fields always match a value from the Reason_Id field in Table 1. I just need to display the Description instead of the ID. Any help is appreciated. I know how to do a simple join where one table field matches another, but in this case, each Reason1,2,3 From table 2 will have a different Reason Id to match to table 1.

Upvotes: 3

Views: 566

Answers (6)

JohnLBevan
JohnLBevan

Reputation: 24470

I believe this version should be more efficient for large tables as it only takes one pass over Table1 rather than 3:

Leaving the answer here should it be of academic interest, but it performs worse than the multiple joins option, so please don't use this:

if OBJECT_ID('Table2') is not null drop table Table2
if OBJECT_ID('Table1') is not null drop table Table1


create table Table1
(
  Reason_Id bigint not null identity(1,1) primary key clustered
  , Reason_Description nvarchar(256)
)
create table Table2
(
    Id bigint not null identity(1,1) primary key clustered
  , Reason1_Id bigint foreign key references Table1(Reason_Id)
  , Reason2_Id bigint foreign key references Table1(Reason_Id)
  , Reason3_Id bigint foreign key references Table1(Reason_Id)
)
insert Table1 select 'Desc 1'
insert Table1 select 'Desc 2'
insert Table1 select 'Desc 3'
insert Table1 select 'Desc 4'

insert Table2 select 1, 2, 3
insert Table2 select 4, 4, 4

select a.id 
, max(case when a.Reason1_Id = b.Reason_Id then b.Reason_Description end)
, max(case when a.Reason2_Id = b.Reason_Id then b.Reason_Description end)
, max(case when a.Reason3_Id = b.Reason_Id then b.Reason_Description end)
from Table2 a
left outer join Table1 b --could do an inner join but left outer is safer
on b.Reason_Id in (a.Reason1_Id, a.Reason2_Id, a.Reason3_Id)
group by a.Id

Here's a SQL Fiddle link comparing the above against the multiple table joins option: http://sqlfiddle.com/#!3/1f5e6/1

Upvotes: 0

Bill Melius
Bill Melius

Reputation: 1053

We do a lot of this kind of process in my office. If you have the ability based on security and server level, use a function. It may be more costly in processor iterations, but if you don't have huge tables (we do this with up to millions of records), an inline scalar function makes for clean code

CREATE FUNCTION dbo.fnc_GetReasonDesc (@ReasonId as int) RETURNS varchar(50) AS
  BEGIN
    DECLARE @rDesc as varchar(50)
    SElECT @rDesc = Reason_Description From Table2 WHERE Reason_id = @ReasonId
    RETuRN @rDesc
  END

Select dbo.fnc_GetReasondesc(Reason1_Id), dbo.fnc_GetReasonDesc(Reason2_Id), ...

Biggest advantage is that no matter how many Id fields you have to work with or how many different tables they may be in, you can always use the same function to convert from id to desc. You can also create an enumeration table and include a "description type" so that if you have say reason descriptions and also problem descriptions, or activity description, etc. you can add an additional field to your "descriptions" table as, say, descriptionType, and then include that value in your function parameters. Now the same table and function can handle any number of different enumerators you may need.

Hope this helps

Upvotes: 0

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

select * from t1 inner join t2 
 on t1.Reason_Id = t2.Reason1_Id
    or t1.Reason_Id = t2.Reason2_Id
    or t1.Reason_Id = t2.Reason3_Id

Take a look at it here http://sqlfiddle.com/#!3/3b9d1/2

Upvotes: 0

Taryn
Taryn

Reputation: 247830

You can basically UNPIVOT the second table and then join on the values. If you don't have an UNPIVOT function then you can use UNION ALL:

select t1.reason_description, t2.col
from table1 t1
left join
(
  select reason1_id value, 't2_reason1_id' col
  from table2
  union all
  select reason2_id value, 't2_reason2_id' col
  from table2
  union all
  select reason3_id value, 't2_reason3_id' col
  from table2
) t2
  on t1.reason_id = t2.value

If you do have UNPIVOT, then you can use something like this:

select t1.reason_description
from table1 t1
(
  select value, col
  from table2
  unpivot
  (
    value for col in (reason1_id, reason2_id, reason3_id)
  ) un
) t2
  on t1.reason_id = t2.value

Upvotes: 0

AJP
AJP

Reputation: 2125

select Reason_Description
from table1 a
Inner join table2 b
  On a.Reason_id = b.Reason1_Id
Union
select Reason_Description
from table1 a
Inner join table2 b
  On a.Reason_id = b.Reason2_Id
Union
select Reason_Description
from table1 a
Inner join table2 b
  On a.Reason_id = b.Reason3_Id

Upvotes: 0

Dan J
Dan J

Reputation: 16718

You'll have to join on the description table three times, once for each field in Table 2.

For example:

SELECT Desc1.Reason_Description AS Reason1_Description, 
       Desc2.Reason_Description AS Reason2_Description, 
       Desc3.Reason_Description AS Reason3_Description 
FROM Table2
JOIN Table1 Desc1 ON Table2.Reason1_Id = Desc1.Reason_Id
JOIN Table1 Desc2 ON Table2.Reason2_Id = Desc2.Reason_Id
JOIN Table1 Desc3 ON Table2.Reason3_Id = Desc3.Reason_Id

Upvotes: 2

Related Questions