none
Abhängige SQL-Abfragen RRS feed

  • Frage

  • Guten Tag,

    ich habe folgendes Problem:

    In einer Spalte einer SQL-Tabelle habe ich die nationalen Vorwahlen, in einer 2. Spalte die entsprechenden Orte von Deutschland und in einer weiteren Spalte die Längen der Vorwahlen stehen: Beispiel

    Vorwahl:  Ort:              Länge:
    030         Berlin            3
    0331       Potsdamm     4
    03301     Oranienburg   5
    03322     Falkensee      5
    033051   Nassenheide   6

    Ich möchte nun eine Abfrage generieren, die bei einer angelieferten Telefonnummer "0330187654" den entsprechenden Ort (hier: Oranienburg) ermittelt.

    Ich könnte mir da folgende Vorgehensweise vorstellen:

    - Prüfe erst die 6 stellige Vorwahl, wenn nicht gefunden, dann die 5 stellige u.s.w.

    Hat hier jemand eine Idee, wie das in einer SQL-Abfrage realisiert werden kann???

    Ich bedanke mich schon mal für evtl. Überlegungen und würde mich über einen Lösungsansatz freuen.

    Mit freundlichem Gruß
    Jürgen

    Mittwoch, 23. September 2015 06:51

Antworten

  • Hallo Jürgen,

    das Speichern der Länge ist etwas redundant, die ergibt sich ja aus der Vorwahl. Und der Abgleich muss gar nicht so kompliziert sein, man muss nur den Teil der Länge der Vorwahl in der Telefonnummer prüfen, z.B.:

    ;WITH data AS
        (SELECT '030' AS Vorwahl, 'Berlin' AS Ort UNION ALL
         SELECT '0331', 'Potsdamm' UNION ALL
         SELECT '03301', 'Oranienburg' UNION ALL
         SELECT '03322', 'Falkensee' UNION ALL
         SELECT '033051', 'Nassenheide')
        ,search AS 
        (SELECT '0330187654' AS Telefon UNION ALL
         SELECT '0301234567' UNION ALL
         SELECT '0172123456')
    SELECT *
    FROM search
         LEFT JOIN data
             ON data.Vorwahl = LEFT(search.Telefon, LEN(data.Vorwahl))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Mittwoch, 23. September 2015 07:07
  • Hallo Olaf,

    ich hab es jetzt doch mit einem CTE hinbekommen:-)

    ;With data as
     (SELECT ONKZ, Ort FROM TestDB].[dbo].[ONKZ_TB])
     ,search AS
      (Select '0330189765' AS Telefon)
    	
    SELECT *
    FROM search
    	LEFT JOIN data
             ON data.ONKZ = LEFT(search.Telefon, LEN(data.ONKZ))

    Vielen Dank für deine Hilfe und schönen Tag noch.

    Gruß Jürgen

    • Als Antwort markiert Jürgen Sch Mittwoch, 23. September 2015 11:19
    Mittwoch, 23. September 2015 11:04

Alle Antworten

  • Hallo Jürgen,

    das Speichern der Länge ist etwas redundant, die ergibt sich ja aus der Vorwahl. Und der Abgleich muss gar nicht so kompliziert sein, man muss nur den Teil der Länge der Vorwahl in der Telefonnummer prüfen, z.B.:

    ;WITH data AS
        (SELECT '030' AS Vorwahl, 'Berlin' AS Ort UNION ALL
         SELECT '0331', 'Potsdamm' UNION ALL
         SELECT '03301', 'Oranienburg' UNION ALL
         SELECT '03322', 'Falkensee' UNION ALL
         SELECT '033051', 'Nassenheide')
        ,search AS 
        (SELECT '0330187654' AS Telefon UNION ALL
         SELECT '0301234567' UNION ALL
         SELECT '0172123456')
    SELECT *
    FROM search
         LEFT JOIN data
             ON data.Vorwahl = LEFT(search.Telefon, LEN(data.Vorwahl))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Mittwoch, 23. September 2015 07:07
  • Hallo Olaf,

    danke zunächst für deinen Lösungsvorschlag und er funktioniert auch super für die vorgegebenen Vorwahlnummern. Gebe ich aber eine andere Nummer mit einer anderen in der Tabelle existierenden Vorwahl ein, so liefert das Ergebnis NULL!
    Wie ich dem Code entnehme, müsste ich demnach alle existierenden Vorwahlen der Tabelle im Abschnitt 'data' mit 'UNION ALL' deklarieren und das wären über 5000 Einträge. Das wäre aber ein Job für einen, der "Mutter und Vater erschlagen hat" :-)

    Gibt es da keine andere Möglichkeit???

    Dann habe ich noch eine 2. Frage:
    Wie bekommt man den Code so schon in dieses Fenster?

    Gruß Jürgen 

    Mittwoch, 23. September 2015 07:28
  • Hallo Jürgen,

    die CTE "data" mit den Vorwahlen sind nur Testdaten, da ich Deine Tabelle ja nicht habe; Du verwendest natürlich Deine Tabelle mit den vorhandenen Vorwahlen.

    Beim Antworten gibt es den Button "Code Block" (zweiter von rechts), damit kann man von der Sprache abhängig formatierten Code posten.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Mittwoch, 23. September 2015 07:36
  • Hallo Olaf,

    danke zunächst für den Tipp mit dem Code Block.

    Leider stecke ich nicht so tief in SQL, dass ich das mit der CTE hinkriege. Kannst du mir da noch mal auf die Sprünge helfen, wie ich das mit meinen beiden Spalten "Vorwahl" und "Ort" in der Tabelle hinkriege???

    Gruß Jürgen

    Mittwoch, 23. September 2015 07:49
  • Vergiss bitte die CTE komplett, wie gesagt, das war nur um Demo Daten zu haben.

    In der Select Abfrage ersetzt Du "data" einfach mit Deiner Tabelle mit den Vorwahlen/Orte.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Mittwoch, 23. September 2015 08:07
  • Ich hab es jetzt wie folgt probiert:

    SELECT data.Vorwahl, data.Ort
    FROM [TestDB].[dbo].[ONKZ_TB] as data
    	LEFT JOIN [TestDB].[dbo].[ONKZ_TB]
             ON data.Vorwahl = LEFT('0330189765', LEN(data.Vorwahl))

    Leider kommt da was Komisches raus! Da wird die gefundene Vorwahl in die Gesamtspalte so oft hinzugefügt wie die Anzahl aller Datensätze ist

    Was habe ich nicht berücksichtigt?

    Gruß Jürgen

    Mittwoch, 23. September 2015 09:06
  • Dann nehme ich mal an, das in Deiner Tabelle die Vorwahlen nicht eindeutig sind; so wie die Ort Hannover & Langenhagen beide die Vorwahl 0511 haben? Dann kommen je Telefonnummer nun mal mehrere Ort bei raus, nur anhand der Nummer kann den genauen Ort so nicht bestimmen.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Mittwoch, 23. September 2015 10:06
  • Ja, das ist richtig, dass die Vorwahlen nicht eindeutig sind. Das ist aber nicht schlimm, denn dann schreibe ich alle zugehörigen Orte in eine Liste.

    Das erklärt aber nicht das Phänomen:

    ONKZ Ort

    ... 02992 Marsberg 02993 Marsberg-Canstein 02994 Marsberg-Westheim 030 Berlin 03301 Oranienburg 03301 Oranienburg 03301 Oranienburg 03301 Oranienburg 03301 Oranienburg 03301 Oranienburg

    ...


    Es werden als Ergebnis der Abfrage alle Nummern ausgegeben und der Eintrag 03301 Oranienburg erscheint in mehr als 5000 Zeilen zusätzlich (s. obigen Ergebnistabellenausschnitt!

    Da stimmt an der Abfrage irgendwas nicht. Denn für die geprüfte Nummer '0330189765' gibt es nur eine eindeutige Vorwahlnummer in der Tabelle.

    Mittwoch, 23. September 2015 10:21
  • Hallo Olaf,

    ich hab es jetzt doch mit einem CTE hinbekommen:-)

    ;With data as
     (SELECT ONKZ, Ort FROM TestDB].[dbo].[ONKZ_TB])
     ,search AS
      (Select '0330189765' AS Telefon)
    	
    SELECT *
    FROM search
    	LEFT JOIN data
             ON data.ONKZ = LEFT(search.Telefon, LEN(data.ONKZ))

    Vielen Dank für deine Hilfe und schönen Tag noch.

    Gruß Jürgen

    • Als Antwort markiert Jürgen Sch Mittwoch, 23. September 2015 11:19
    Mittwoch, 23. September 2015 11:04
  • Die JOIN Bedingung ist falsch, da es eine Konstante ist und damit einen Cross-Join der ONKZ_TB und der einen Zeile aus der selben Tabelle ergibt. Kürzen wir das etwas

    SELECT data.Vorwahl, data.Ort
    FROM [TestDB].[dbo].[ONKZ_TB] as data
    WHERE data.Vorwahl = LEFT('0330189765', LEN(data.Vorwahl))

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu


    Mittwoch, 23. September 2015 11:36
    Beantworter
  • Hallo Christoph,

    danke für die Verbesserung. Dein Code funzt und ist auch schön kurz.

    Gruß Jürgen

    Mittwoch, 23. September 2015 12:04