CODEWITHSUNDEEP
sql-servert-sql
user3813482
user3813482

Reputation: 13

Transact SQL Returning Data based on foreign key

Have a table A that has two columns, each a foreign key to other tables where key data can be stored. need to pull back that key data such as name based on those columns. Can do a left join to each table but that will include a lot of null values so want to avoid that. The foreign keys are not using nulls because a key could potentially exist for each value - but we do not use the system that way, so one field has a value the other a zero.

Example :

Table A:

Index  App   Ten
----------------
  1     123    0
  2     124    0
  3      0     125
  4      0     126

Table App:

Index  LName
---------------
 123     Jones
 124     Smith

Table Ten:

Index   Lname
---------------
 125      Doe
 126      Williams

What is the proper way to join these to select the table based on the index (ignore if zero) and return the data needed without left join and null values?

Desired output:

Index  App   Ten  Lname
------------------------------
  1     123    0    Jones
  2     124    0    Smith
  3      0     125  Doe
  4      0     126  Williams

Upvotes: 1

Views: 41

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select a.[index], a.App, 0 as [Ten], App.Lname 
  from a 
  join App 
    on a.App = App.[Index] 
 where a.App <> 0
intersect 
select a.[index], 0 as [App], a.Ten, Ten.Lname 
  from a 
  join Ten
    on a.Ten = Ten.[Index] 
 where a.Ten <> 0
 order by 1

Get the values from App and then intersect them with Ten
<> ignore the 0 vlues

Upvotes: 1

CSharper
CSharper

Reputation: 5580

Select A.Ind, A.App, A.Ten, coalesce(ap.LName,t.LName) 
from A 
LEFT JOIN App ap on ap.Ind = A.App 
LEFT JOIN Ten t on A.Ten = t.Ind

Coalesce will select the first value, if the first value is null, it will select the second value

Upvotes: 3

Related Questions