conditional join?

  • Thread starter Thread starter Peter Carlson
  • Start date Start date
P

Peter Carlson

The following sql selects a "person"
SELECT A.*, (E.last+', ') & (E.first) & (' '+E.middle) AS
emergency_fullname, S.status AS statust, R.rank AS rankt FROM ((person AS A
LEFT JOIN Person AS E ON A.emergency = E.id) INNER JOIN tPersonnel_Status AS
S ON A.status = S.id) INNER JOIN tPersonnel_Rank AS R ON A.rank = R.id WHERE
A.id=%id%;

%id% being replaced with the record ID of the person

however there are times when A.status or A.rank is = 0 (or no status, no
rank) A.status and A.rank are integers that point to autonumber id's in the
appropriate status and rank tables. When status or rank = 0 then the
"person" record is not retrieved becauase it cant find an appropriate entry
in the status or rank tables. Is there a way to do this? What I need is
basically this:

tPerson
id, last, emergency, status, rank
1, carlson, 2, 1, 1
2, smith, 0, 0, 0

tStatus
id, status
1, active
2, retired

tRank
1, Chief
2, Asst. Chief

select id1=
1, carlson, 2, active, Chief
select id2=
2, smith, 0, '', ''

I'd like to keep it as generic sql as possible since this *might* hit other
databases, but primarily hits access.

Peter
 
Can you add records to the Status and Rank tables that equate to zero?
Something like:
0; "undefined"

That way you will have the records returned. Otherwise, you will need to use a
non-equi join. That MIGHT be something like the UNTESTED code below. I don't
know if it will work, but you can give it a try. It could just blow up.

SELECT A.*, (E.last+', ') & (E.first) & (' '+E.middle) AS
emergency_fullname, S.status AS statust, R.rank AS rankt
FROM ((person AS A LEFT JOIN Person AS E ON A.emergency = E.id)
INNER JOIN tPersonnel_Status AS S ON A.Status = IIF(A.Status = 0,0,S.id))
INNER JOIN tPersonnel_Rank AS R ON A.rank = IIF(A.Rank = 0,0,R.id)
WHERE A.id=%id%;
 
Back
Top