Nur die Telefonnummer vom ersten Ansprechpartner eines Kunden ermitteln

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

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

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

[BlueStarHH]

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

Ja, stimmt Du hast recht! Denkfehler von mir.