Nur die Telefonnummer vom ersten Ansprechpartner eines Kunden ermitteln

[BlueStarHH]

Zitat:

Zitat von itsChris (Beitrag 1527693)

Die vorherigen Antworten sollten beide funktionieren. Ich hätte noch eine dritte im Angebot:

Code:

SELECT
    outerAP.KdNr,
    (
        SELECT FIRST 1 TelefonNr
        FROM Ansprechpartner AS innerAP
        WHERE innerAP.KdNr = outerAP.KdNr
        ORDER BY (
            CASE 
                WHEN innerAP.Standard THEN 1
                ELSE 2
            END
        ),
        innerAP.ID DESC
    ) AS TelefonNr,
    (
        SELECT FIRST 1 FaxNr
        FROM Ansprechpartner AS innerAP
        WHERE innerAP.KdNr = outerAP.KdNr
        ORDER BY (
            CASE 
                WHEN innerAP.Standard THEN 1
                ELSE 2
            END
        ),
        innerAP.ID DESC
    ) AS FaxNr
FROM Ansprechpartner AS outerAP
GROUP BY outerAP.KdNr;

Danke auch dafür. Wenn später mal weitere Felder hinzukommen (z.B. Geburtstag, E-Mail-Adresse, usw.), wäre das schwieriger zu erweitern und evtl. auch nicht so performant wie die anderen Lösungen.

[Jumpy]

Zitat:

Zitat von BlueStarHH (Beitrag 1527696)

Danke, das läuft. Aber es gibt mir ca. 300 Datensätzen weniger als in der Ansprechpartner-Tabelle sind (select count(*) from Ansprechpartner)
Warum könnten bei Deinem Code Datensätze fehlen?

Das Statement liefert ja pro Kunde nur einen Ansprechpartner und zwar den mit der neusten ID pro Kunde, und da es wahrsch. Kunden gibt, mit mehreren Ansprechpartnern, kommt halt von denen nur einer und somit kommen nicht alle Personen die in der Ansprechparnter Tabelle sind. Dachte das wär das Ziel.

[BlueStarHH]

Zitat:

Zitat von Jumpy (Beitrag 1527701)

Zitat:

Zitat von BlueStarHH (Beitrag 1527696)

Danke, das läuft. Aber es gibt mir ca. 300 Datensätzen weniger als in der Ansprechpartner-Tabelle sind (select count(*) from Ansprechpartner)
Warum könnten bei Deinem Code Datensätze fehlen?

Das Statement liefert ja pro Kunde nur einen Ansprechpartner und zwar den mit der neusten ID pro Kunde, und da es wahrsch. Kunden gibt, mit mehreren Ansprechpartnern, kommt halt von denen nur einer und somit kommen nicht alle Personen die in der Ansprechparnter Tabelle sind. Dachte das wär das Ziel.

Ja, stimmt Du hast recht! Denkfehler von mir.

[BlueStarHH]

Ich brauche auch noch eine zweite Abfrage aufbauend auf der hier gefragten: Die Ansprechpartnerdaten (Tel, Fax) sollen mit allen Feldern der Kundentabelle in einer Abfrage zurückgegeben werden. Ich habe es mit den Lösungen von Jumpy und rapante probiert. Beide Abfragen habe ich nach 5 Minuten Laufzeit abgebrochen. Warum ist das so langsam? Wie gehts besser? Für sich laufen die Ansprechpartner-Abfragen schnell, aber sobald die mit den Kunden verbunden werden, extram langsam.

Der Code von Jumpy mit Kunden:

SQL-Code:

WITH BASIS AS (
-- Kunden mit Standard-Ansprechpartner
Select KdNr, max(id) as id
from Ansprechpartner
Where Standard=true
Group By KdNr

UNION

-- Kunden ohne Standard-Ansperchpartner
Select KdNr, max(id) as id
from Ansprechpartner
Where KdNr not in (Select Distinct KdNr From Ansprechpartner Where Standard=true)
Group By KdNr
)

select *
from Kunde k

left join (

   Select A.KdNr, A.TelefonNr, A.FaxNr
   From Basis B
   Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID

) sub on sub.KdNr = k.KdNr

where k.aktiv

oder der Code von Rapante mit Kunden

SQL-Code:

WITH ap AS (
    SELECT Ansprechpartner.KdNr,
           Ansprechpartner.TelefonNr,
           Ansprechpartner.FaxNr,
           ROW_NUMBER() OVER(PARTITION BY Ansprechpartner.KdNr
                              ORDER BY standard DESC, id DESC) AS rk
      FROM Ansprechpartner)

select *
from Kunde k

left join (

  SELECT ap.*
  FROM ap
  WHERE ap.rk = 1

) sub on sub.KdNr = k.KdNr

where k.aktiv

[rapante]

Wie sehen denn die Tabellendefinitionen aus?
Ist KdNr als Foreign Key definiert, bzw. gibt es Indexe?
Wieviele Datensätze sind in den Tabellen vorhanden?

[Jumpy]

Wie sieht's so aus?

SQL-Code:

Select K.*, A.TelefonNr, A.FaxNr
from Kunde k
Left join Basis B on B.KdNr=K.KdNr
Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID

[BlueStarHH]

Zitat:

Zitat von rapante (Beitrag 1527707)

wie sehen denn die tabellendefinitionen aus?
Ist kdnr als foreign key definiert, bzw. Gibt es indexe?
Wieviele datensätze sind in den tabellen vorhanden?

SQL-Code:

create table ansprechpartner (
    id          integer not null,
    kdnr        varchar(15) character set ascii not null,
    name        varchar(50),
    telefonnr   varchar(20),
    faxnr       varchar(20),
    ...
);

alter table ansprechpartner add constraint pk_ansprechpartner primary key (id);
alter table ansprechpartner add constraint fk_ansprechpartner_1 foreign key (kdnr) references kunde (kdnr) on delete cascade on update cascade;


create table kunde (
    kdnr                    varchar(15) character set ascii not null,
   ..
);

alter table kunde add constraint pk_kunde primary key (kdnr);

Jeweils über 10.000 Datensätze

[BlueStarHH]

Zitat:

Zitat von Jumpy (Beitrag 1527708)

Wie sieht's so aus?

SQL-Code:

Select K.*, A.TelefonNr, A.FaxNr
from Kunde k
Left join Basis B on B.KdNr=K.KdNr
Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID

Dank! Das klappt und läuf schnell. Ich glaube die letzte Zeile kann auch so geändert werden:

Alt: Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID
Neu: Left Join Ansprechpartner A ON A.ID=B.ID

Die (Ansprechpartner-)ID ist ja schon eindeutig einer KdNr zugeordnet?!

[Jumpy]

Zitat:

Zitat von BlueStarHH (Beitrag 1527716)

Zitat:

Zitat von Jumpy (Beitrag 1527708)

Wie sieht's so aus?

SQL-Code:

Select K.*, A.TelefonNr, A.FaxNr
from Kunde k
Left join Basis B on B.KdNr=K.KdNr
Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID

Dank! Das klappt und läuf schnell. Ich glaube die letzte Zeile kann auch so geändert werden:

Alt: Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=B.ID
Neu: Left Join Ansprechpartner A ON A.ID=B.ID

Die (Ansprechpartner-)ID ist ja schon eindeutig einer KdNr zugeordnet?!

Stimmt, das war doppelt gemoppelt. Schadet zwar nicht und wird vielleicht sogar bei der Ausführung von der Datenbank wegoptimiert, aber die Optimierung klappt ja nicht immer oder braucht manchmal Hilfe, wie du an deinem ursprünglichen Statement, dass so lange dauert, gesehen hast.