I need to know where Xy Street can be found and I have 3 tables.
select t.nev
from hospital.person sz, hospital.place t, hospital.member ti
where 1=1
and sz.residence_placeid=t.placeid
and sz.residence_placeid=ti.placeid
and t.placeid=ti.placeid
and t.street like 'Xy Street %'
order by t.street
Addresses can also be found in table sz and ti. My question is how to append those to the criteria (like union or something like that)
First, you should eschew using commas in your FOR statement because it makes the query hard to read, and this style of query has been deprecated. Instead, use an explicit JOIN. Regarding your question, if the other two tables also have a street column for the address, then you can simply add two more conditions to your WHERE clause. Here is what your revamped query might look like:
SELECT t.nev
FROM hospital.person sz INNER JOIN hospital.place t
ON sz.residence_placeid = t.placeid
INNER JOIN hospital.member ti
ON sz.residence_placeid = ti.placeid AND t.placeid=ti.placeid
WHERE t.street like 'Xy Street %' OR
sz.street LIKE 'Xy Street %' OR
ti.street LIKE 'Xy Street %' OR
ORDER BY t.street