if one field is null i want to look at another field in my link

[sbcsandhu]

hi , i forgot how to do this

i have a table called wia
and a table called indv

the table wia doesnt have SSN all the time, so then what i want to do is a first name, last name , dob match

so currently i have

select *
from wia
inner join indv on wia.ssn = indv.ssn

but if the ssn in wia is null
i want to do the first name, last name, dob link.
only if the ssn in wia is null

Thanks in advance

 

[jymm]

left join gets you all records from wia regardless if there is one in indv

this what you want?

 

[sbcsandhu]

no, becuase i cant get info from the indv table, if my wia table has no SSN,
so i want to do another link if there is no SSN,
last name and DOB.

even if i do a left join, and there is no SSN on WIA, i wont get anything back from indv, but i want to get info from indv with last name and dob, if there is no ssn in wia table.

hope im clear

 

[xlbo]

What exactly does this have to do with SSRS?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jymm]

ok - so it has more to do with SQL, but...

do two selects - one that gets those folks that have the ssn in the SSRS report... and one that does not... union them togeather...

make sense?

 

[AlexCuse]

next time post this in forum183.

But use a case statement in your join, like this.

inner join indv on 
(wia.ssn = indv.ssn AND wia.ssn is null) 
OR (wia.ssn is null AND wia.fname = indv.fname AND wia.lname = indv.lname AND wia.DOB = indv.DOB)

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century