It's late on Friday, my brain is fried, our DB guru is out today, and the solution is evading me so far. Do I need to use a sub-query instead of left joins, or what? :queasy:
The tables and columns of interest:
review
-----------
review_id (PK)
predicate_id (FK)
predicate
-------------
predicate_id (PK)
token
facility_1pk_token
predicate_type_id (FK)
predicate_type
--------------------
predicate_type_id (PK)
predicate_email
---------------------
token (same as predicate.token)
email
predicate_type_id (FK)
What I want to do is get the email for the predicate associated with a given review.review_id. If that predicate does not have an email in the predicate_email table, then I want to look for one for a predicate whose token is the same as the value for the first predicate's facility_1pk_token. This was my last attempt, which returned 0 rows, when (ideally) it should have found an email following the whole path to the predicate whose predicate.token is the same as the review's predicate.facility_1pk_token:
select coalesce(pe.email, pe2.email) as email
from review as r
inner join predicate as p on r.predicate_id = p.predicate_id
inner join predicate_email as pe on p.token = pe.token
left join predicate as p2 on p.facility_1pk_token = p2.token
left join predicate_email as pe2 on p2.token = pe2.token
where
r.review_id = 2063750
and
pe.predicate_type_id = p.predicate_type_id
and
pe2.predicate_type_id = p.predicate_type_id
We're using PostgreSQL, if that matters for any suggested fixes/soultions.
