I have a table, let's call bl. bl has the following fields
ci int4 (a foreign key to table comp)
I also have table comp with the following fields:
I'd like to write something like the following query:
select coalesce('Some Text: ' || b.name || '\n', '') ||
coalesce ('Some other text: ' || c.name || '\n', '') ||
coalesce ('Yet more text: ' || d.name, '') as name, ji
from bl as a
join comp as b on (a.ci = b.ci and a.wh)
join comp as c on (a.ci = c.ci and a.wv)
join comp as d on (a.ci = d.ci and a.wt)
where ji = 4
There are multiple entries in bl for a given value of ji, but only one of them has wh true, and only one has wv true and only one has wt true. I would like one query that finds those records in bl where wh, wv, or wt is true and join with comp to print out the correct name.
This works if I use join only once, it either retrurns no records, or a lot of records if I start doing the second and third joins.
Can anyone help me with how this query should look?