/* Kreis Lippe, Jörg Burzeja - ohne Gewähr
   Jugendhilfeanbieter aus neuer Anbieterdatenbank
   weitere Werte im select mit Bezeichner FK-Tabelle aufnehmen */

select 'Pflegestelle' AS Typ,
        a.nachname,
        a.vorname,
        a.strasse,
        a.plz,
        a.ort,
        isnull ((a2.nachname + ','), '')  + (' ') + isnull (a2.Vorname, '') AS [Ansprechpartner]

from n_adressen a

-- joins auf die fk-Tabellen

        left join n_anbieter ab ON (a.adressnummer = ab.adressref)
        left join n_anbiet2aufnbed ab_af ON (ab.lfdnr = ab_af.anbieter_fk)
        left join n_anbiet2ausbild ab_ab ON (ab.lfdnr = ab_ab.anbieter_fk)
        left join n_anbiet2auskrit ab_ak ON (ab.lfdnr = ab_ak.anbieter_fk)
        left join n_anbiet2bildang ab_ba ON (ab.lfdnr = ab_ba.anbieter_fk)
        left join n_anbiet2eignung ab_eg ON (ab.lfdnr = ab_eg.anbieter_fk)
        left join n_anbiet2geolage ab_gl ON (ab.lfdnr = ab_gl.anbieter_fk)
        left join n_anbiet2rechtsf ab_rf ON (ab.lfdnr = ab_rf.anbieter_fk)
        left join n_anbiet2schular ab_sa ON (ab.lfdnr = ab_sa.anbieter_fk)
        left join n_anbiet2schuext ab_se ON (ab.lfdnr = ab_se.anbieter_fk)
        left join n_anbieter2einart ab_ea ON (ab.lfdnr = ab_ea.anbieter_fk)
        left join n_anbieter2scoring ab_sc ON (ab.lfdnr = ab_sc.anbieter_fk)
        left join n_anbieter2zusatza ab_za ON (ab.lfdnr = ab_za.anbieter_fk)
        left join n_ansprech ap ON (ap.adressnummer = ab.lfdnr)
        left join n_htier2anbiet ab_ht ON (ab.lfdnr = ab_ht.anbieter_fk)
        left join n_pflegestelle ps ON (a.adressnummer = ps.adressref)
        left join n_pflegestelleform psf ON (ps.adressref = psf.pflegestelleref)
        left join n_infraverkehr ifk ON (ps.adressref = ifk.infraref)
        left join n_pflegepersonen pps ON (pps.pflegestelleref = psf.pflegestelleref)


-- joins auf die Tabelle mit den Werten

        -- Zielgruppen / Symptome
        left join n_heimeaufnbed h_af ON (h_af.idaufn = ab_af.zielgruppe_fk)
        -- Ausbildungsmöglichkeiten
        left join n_heimeausbild h_ab ON (h_ab.idausb = ab_ab.ausbildungsmoeglic)
        -- Ausschlusskriterien
        left join n_ausschlusskriter ak ON (ak.lfdnr = ab_ak.ausschlusskriterie)
        -- Bildungsangebote intern, Bildungsangebote / Lehrgänge
        left join n_bildungsangebot ba ON (ba.lfdnr = ab_ba.bildungsangebot_fk)
        -- Ausrichtungen
        left join n_heimeeignung he ON (he.ideig = ab_eg.ausrichtung_fk)
        -- Geographische Lagen
        left join n_geographische_la gl ON (gl.lfdnr = ab_gl.geographischelage_)
        -- Rechtsformen / Weltanschauung
        left join n_rechtsform_welta wa ON (wa.lfdnr = ab_rf.rechtsformweltansc)
        -- Bildungsangebote intern, Schularten
        left join n_schularten sa ON (sa.lfdnr = ab_sa.schulart_fk)
        -- Bildungsangebote extern, Schularten
        left join n_schularten sa2 ON (sa2.lfdnr = ab_se.schulartext_fk)
        -- Einrichtungsarten
        left join n_heimeeinart ha ON (ha.lfdnr = ab_ea.einrichtungsart_fk)
                -- Zusatzmodul Scoring hier nicht vorhanden
        -- Zusatzangebote
        left join n_zusatzangebot za ON (za.lfdnr = ab_za.zusatzangebot_fk)
        -- Anprechpartner
        left join n_adressen a2 ON (ap.ansprechpartner = a2.adressnummer)
        -- Entgelte
        left join n_entgelt eg ON (eg.anbieterjh_fk = ab.lfdnr) 
        -- Kapazität, Freie Plätze
        left join n_pflegeplatz pp ON (pp.pflegestelleref = ab.lfdnr)

        -- Pflegestelle - Pflegeformen
        left join n_pflegeform pf ON (pf.lfdnr = psf.pflegeformref)
        -- Pflegestelle - Allgemeine Daten, Besondere Eignung
        left join n_pflegeeignung pe ON (pe.lfdnr = psf.pflegeformref)
        -- Pflegestelle - Infrastrukur, Verkehrsmittel
        left join n_verkehrsmittel vm ON (vm.lfdnr = ifk.verkehrref)
        -- Pflegestelle - Infrastruktur, Wohnform
        left join n_wohnform wf ON (wf.lfdnr = ifk.verkehrref)
        -- Pflegestelle - Infrastruktur, Haustiere
        left join n_haustier ht ON (ht.lfdnr = ab_ht.haustier_fk)
        -- Pflegestelle - Pflegekinder
        left join n_pflegekinder pk ON (pk.pflegestelleref = ps.adressref)
        -- Pflegestelle - Pflegepersonen
        left join n_adressen a3 ON (pps.adressref = a3.adressnummer)


where ab.anbietertyp = 'p'

group by a.nachname,
        a.vorname,
        a.strasse,
        a.plz,
        a.ort,
        ab.anbietertyp,
        isnull ((a2.nachname + ','), '')  + (' ') + isnull (a2.Vorname, '')

order by 1,2,6,5,3,4