Nur die Telefonnummer vom ersten Ansprechpartner eines Kunden ermitteln

[BlueStarHH]

Hallo,

ich habe eine Tabelle mit Ansprechpartnern. Dabei kann es je Kundennummer beliebig viele Ansprechpartner geben. Ich möchte nun eine Liste mit allen Kundennummern und je Kundennummer genau eine Telefonnumer und Faxnummer. Und zwar die Telefonnumer vom Standard-Ansprechpartner (Bool Feld Standard = true) oder wenn es keinen Standard-Ansprechpartner gibt, den Ansprechpartner der zuletzt angelegt wurde (kleinste ID). Ich habe das probiert, aber läuft ewig und es kommen zwar alle Ansprechpartner zurück aber insgesamt hat nur einer eine Telefonnummer in der Rückgabe:

SQL-Code:

select ap.KdNr, sub.TelefonNr, sub.FaxNr
  from Ansprechpartner ap
  left join
  (
   select first 1 TelefonNr, FaxNr, KdNr
   from Ansprechpartner
   order by standard desc, ID desc
  ) sub
  on sub.KdNr = ap.KdNr
  where Aktiv

[Billa]

select "first 1" ... hilft Dir weiter, etwa so:

...
select first 1 ap.KdNr, sub.TelefonNr, sub.FaxNr
from Ansprechpartner ap
left join sub on ub.KdNr = ap.KdNr
order by sub.standard desc, sub.ID desc
where ap.KdNr = :KDNR
...

VG

[BlueStarHH]

Zitat:

Zitat von Billa (Beitrag 1527683)

select "first 1" ... hilft Dir weiter, etwa so:

...
select first 1 ap.KdNr, sub.TelefonNr, sub.FaxNr
from Ansprechpartner ap
left join sub on ub.KdNr = ap.KdNr
order by sub.standard desc, sub.ID desc
where ap.KdNr = :KDNR
...

VG

Ich möchte das ganze nicht nur für einen Kunden haben. Sondern für *alle* Kunden. Deine Abfrage wird nur einen Datensatz für einen Kunden geben.

[rapante]

Moin,
ein einfacher SELECT mit GROUP BY sollte schon reichen:

Code:

  SELECT KdNr, TelefonNr, FaxNr
  FROM Ansprechpartner
  WHERE Aktiv
  GROUP BY KdNr
  ORDER BY standard DESC, ID DESC

bzw, wenn alle kunden auch ohne Ansprechpartner gelistet werden sollen:

Code:

  SELECT kunden.KdNr, ap.TelefonNr, ap.FaxNr
  FROM kunden LEFT JOIN
    (SELECT KdNr, TelefonNr, FaxNr
     FROM Ansprechpartner
     WHERE Aktiv
     GROUP BY KdNr
     ORDER BY standard DESC, ID DESC) ap
  ON kunden.KdNr = Ansprechpartner.KdNr

[BlueStarHH]

Zitat:

Zitat von rapante (Beitrag 1527685)

Moin,
ein einfacher SELECT mit GROUP BY sollte schon reichen:

Code:

  SELECT KdNr, TelefonNr, FaxNr
  FROM Ansprechpartner
  WHERE Aktiv
  GROUP BY KdNr
  ORDER BY standard DESC, ID DESC

Gibt diese Meldung:

Zitat:

Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).

[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:

Zitat von Jumpy (Beitrag 1527691)

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?

[BlueStarHH]

Zitat:

Zitat von rapante (Beitrag 1527687)

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;

Super, das läuft und alle Datensätze sind da.

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