Обсуждение: JOIN exclusion problem
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
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: > > 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
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"
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
_____________________________
>In this case you should try a union like
>SELECT ... FROM ap, k WHERE ap.kunden_nr = k.kunden_nr
>UNION
>SELECT ... FROM ap, ag WHERE ap.agentur_nr = ag.agentur_nr
>ORDER BY nachname;
>
>(Note the target lists.)