Re: [GENERAL] JOIN exclusion problem

On Thu, Jul 01, 1999 at 03:43:27PM +0200, Anja Speerforck wrote:
> At 11:24 01.07.99 +0100, you wrote:
> >I'm not sure that I understand exactly what you are trying to do. I'm
> >guessing (and I mean guessing) that the tables are something like:
> >
> >ansprechpartner: private owner
> >kunden: client
> >agenturen: estate agent
> >
> The tables represent:
>
> ansprechpartner = contact person
> kunden = clients
> agenturen = agencies
>
> What I'm trying to do is get a list of all contact people, whether they're
> associated with a specific client or a specific agency, so that the name of
> the client or agency shows up in the results.
> -------

Ah, now it's clear. You're looking for the UNION operator, and two selects.
Something like:

SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax,
ap.email, ap.kunden_nr, k.name1
FROM ansprechpartner ap, kunden k
WHERE ap.kunden_nr = k.kunden_nr
UNION
SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax,
ap.email, ap.agentur_nr, ag.name1
FROM ansprechpartner ap, agenturen ag
WHERE ap.agentur_nr = ag.agentur_nr
ORDER BY nachname

Hope this helps,
Ross