JOIN exclusion problem

On Wed, 30 Jun 1999, Anja Speerforck wrote:

Hello,

I'm trying to join three tables, where I need only one piece of data from
two of the tables. The code I wrote is:

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

^^
You might wanna try an AND there.

ORDER BY nachname"

The problem is that each row from each table is combined with each row of
the other tables. I know the code is wrong, but I don't know how to write
it so that the results show only the actual value of ag.name1 and k.name1,
and not all of the possible combinations.

Is there a way of limiting how the rows are built up? Pardon for novice
nature of this question...

--
Peter Eisentraut
PathWay Computing

Try using AND instead of OR.

Anja Speerforck wrote:

Thank you both for your replies. Unfortunately, the AND solution doesn't
work since individuals who have a value for ap.kunden_nr are not likely to
have one for ap.agentur_nr -- they are two fairly distinct groups. When I
insert AND, the results are blank.

Any other suggestions?

At 16:23 01.07.99 +1000, you wrote:

Try using AND instead of OR.

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

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

^^
You might wanna try an AND there.

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

So are you trying to get is get details on all properties both privately
rented and rented via an agency?

I think a \d of all the tables involved would be useful to make sense of it
all.

In the interim though, if you only what unique combinations of ag.name1 and
k.name1 you could try:

SELECT DISTINCT k.name1, ag.name1
FROM ansprechpartner ap, kunden k, agenturen ag
WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr;

But somethings tells me that's not what you actually wanted!

Hello,

I'm trying to join three tables, where I need only one piece of data from
two of the tables. The code I wrote is:

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

The problem is that each row from each table is combined with each row of
the other tables. I know the code is wrong, but I don't know how to write
it so that the results show only the actual value of ag.name1 and k.name1,
and not all of the possible combinations.

Is there a way of limiting how the rows are built up? Pardon for novice
nature of this question...

Thanks in advance,

Anja

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

The problem is you've got two different result sets you need here. For
example, what would you expect to find in the k.name1 column if
k.kunden_nr is blank or null? The database doesn't know, and it can't
process the query without an answer.

You either need to break it into two separate queries or else make it
into a UNION. If it's a UNION you will need to specify explicity what to
put into k.name when joining with agenturen. I'm guessing that you want
it blank in such a case.

Anja Speerforck wrote:

Thank you both for your replies. Unfortunately, the AND solution doesn't
work since individuals who have a value for ap.kunden_nr are not likely to
have one for ap.agentur_nr -- they are two fairly distinct groups. When I
insert AND, the results are blank.

Any other suggestions?

At 16:23 01.07.99 +1000, you wrote:

Try using AND instead of OR.

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

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

^^
You might wanna try an AND there.

ORDER BY nachname"

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

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.
-------

In the interim though, if you only what unique combinations of ag.name1 and
k.name1 you could try:

SELECT DISTINCT k.name1, ag.name1
FROM ansprechpartner ap, kunden k, agenturen ag
WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr;

But somethings tells me that's not what you actually wanted!

You're right! I need pretty much all of the data from ap, so that doesn't
work. I'm trying some of the other suggestions that just came in next to
see if they work.

Thanks for your help,

Anja

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

At 12:42 01.07.99 +0200, you wrote:

select ... from a, b where a.x=b.x
union
select ... from a, c where a.x=c.x

is this what you wanted to achieve?

Actually, more like

select ... from a, b where a.x=b.x
union
select ... from a, c where a.y=c.y

As Chris Bitmead wrote:

The problem is you've got two different result sets you need here.....

You either need to break it into two separate queries or else make it
into a UNION. If it's a UNION you will need to specify explicity what to
put into k.name when joining with agenturen. I'm guessing that you want
it blank in such a case.

Which is correct. I did try it as a UNION, and this didn't work since I do
need k.name to be blank. I believe the solution is in fact two separate
queries. We're trying that now....

Anja

Thanks Peter and Ross for suggesting the correct solution. It works with
the following code:

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

Best regards,

Anja

At 09:49 01.07.99 -0500, you wrote:

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

_____________________________