Nur die Telefonnummer vom ersten Ansprechpartner eines Kunden ermitteln

[rapante]

Ach Sorry - ich hatte vergessen, dass das bei Firebird so nicht funktioniert mit dem GROUP BY:

In Firebird müsste das über row_number() gehen (Ich kann es leider gerade nicht testen):

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 ap.*
  FROM ap
 WHERE ap.rk = 1;

[Jumpy]

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 A.KdNr, A.TelefonNr, A.FaxNr
From Basis B
Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=Bi.ID

Ich würde pro Kunde erstmal den Ansprechpartner ermitteln, der die Kriterien erfüllt (und da gibt es hier 2 disjunkte Wege), und dann von dem die Daten holen

[itsChris]

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;

[BlueStarHH]

Zitat von itsChris:

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.

[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 von 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?

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 von Jumpy:

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 A.KdNr, A.TelefonNr, A.FaxNr
From Basis B
Left Join Ansprechpartner A ON A.KdNr=B.KdNr AND A.ID=Bi.ID

Ich würde pro Kunde erstmal den Ansprechpartner ermitteln, der die Kriterien erfüllt (und da gibt es hier 2 disjunkte Wege), und dann von dem die Daten holen

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?

[Jumpy]

Zitat von BlueStarHH:

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.