combining queries

[kubla]

I've been banging my head against a wall over this problem for several days now.

I have three tables: holdings, letters and correspondents

I'm trying to extract data from all three to match a string entered by a user.

My query looks up an id from one table and matches it to a result in the others.

This query works:

select holdings.holder, letters.dates, letters.rec_id, correspondents.corr_id, letters.number_of_letters, correspondents.correspondent from correspondents, letters, holdings where correspondents.correspondent like '%[user entered string]%' and correspondents.corr_id=letters.corr_id and holdings.holder_id=letters.address

I would like to extract results where correspondents.corr_id = letters.corr_id OR letters.rec_id

Any ideas on how I can do this?

 

[petersJazz]

Try setting it up as if it would be four tables using self-join:

select c.xxxx,
l1.xxxx,
l2.xxxx,
h.xxxx
from correspondents c,
letters l1,
letters l2,
holdings h
where condition;

 

[Broccoli2]

A left join might be the solution you are looking for:

SELECT
holdings.holder,
letters.dates,
letters.rec_id,
correspondents.corr_id,
letters.number_of_letters,
correspondents.correspondent 
FROM correspondents
LEFT JOIN letters ON (correspondents.corr_id=letters.corr_id OR correspendents.corr_id=letters.rec_id)
LEFT JOIN holdings ON letters.address=holdings.holder_id
WHERE
correspondents.correspondent like '%[user entered string]%';

-Rob