sql - JOIN'ing by 3 tables and retrieving field based on content of those tables -

kind of hard explain, i'll map out. given these 4 tables: houses, landlord_houses, company , tenant, need find houses have signed in last 14 days , information them.

previously i've done simple select query houses table. need letting agent of house display on report. problem is, letting agent can in 1 of 3 locations: in company table, houses table or in tenant table. i've come query far:

select distinct h.id id,     h.address1 address1,     h.town town,     h.postcode postcode,     h.valid_from valid_from,     h.valid_to valid_to,     (case when c.name not null c.name || ' (ms)'       when h.letting_agent not null h.letting_agent       when t.id not null t.letting_agent       else 'unknown (not set yet)' end) agent houses h left join landlord_houses lh on lh.house_id = h.id left join company c on c.id = lh.company_id left join tenant t on t.house_id = h.id h.deleted false , h.archived false , h.sign_up_complete true , h.completed > now() - '14 days'::interval order h.id 

this kind of works, i'm getting results have empty agent field though it's meant "unknown (not set yet)". i'm getting duplicate houses returned though i've used distinct h.id. think because there multiple letting agents these houses in company, houses , tenant tables.

what needs changed in query work?

thank you.

that case statement looks little wonky. may returning multiple values. try this:

coalesce(c.name, h.letting_agent, t.letting_agent, 'unknown (not set yet)') agent 

this checks c.name. if null, move next argument , same.

everything else in query looks fine , work in postgresql.