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:

markieren

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

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

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

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.

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

markieren

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:

markieren

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

Zitat von rapante:

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

markieren

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

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

markieren

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;

zusammenfalten · markieren

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

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

markieren

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;

Zitat von Jumpy:

zusammenfalten · markieren

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?

Zitat von 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):

markieren

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.