MSSQL Spaltennamen bei Mehrfachabfrage?

[omata]

und noch ein Versuch...

SQL-Code:

SELECT a.familienstand AS famstand, 
       COUNT(b.kunden_id) AS AnzVerträge,
       (SELECT COUNT(kunden_id) 
        FROM kind 
        WHERE kunden_id = 7) AS AnzKinder,
       (SELECT COUNT(kunden_id) 
        FROM rel_kuka  
        WHERE kunden_id = 7) AS AnzKampagnen,
       (SELECT COUNT(kunden_id) 
        FROM einkommen  
        WHERE kunden_id = 7
          AND von = (SELECT MAX(von)
                     FROM einkommen 
                     WHERE kunden_id = 7)) AS EKjahr
FROM familienstand a
INNER JOIN vertrag b 
  ON b.kunden_id = a.kunden_id
WHERE a.kunden_id = 7
  AND a.von = (SELECT MAX(von)
               FROM familienstand
               WHERE kunden_id = 7)
GROUP BY a.familienstand

und noch ein anderer Vorschlag, der gleich für alle Kunden gilt...

SQL-Code:

SELECT a.kunden_id, 
       a.familienstand AS famstand, 
       COUNT(*) AS AnzVerträge,
       kinder.anzahl AS AnzKinder,
       kampagnen.anzahl AS AnzKampagnen,
       ekjahr.anzahl AS EKjahr
FROM familienstand a
INNER JOIN vertrag b 
  ON b.kunden_id = a.kunden_id
LEFT JOIN (SELECT kunden_id, COUNT(*) anzahl
           FROM kind 
           GROUP BY kunden_id) kinder
  ON a.kunden_id = kinder.kunden_id
LEFT JOIN (SELECT kunden_id, COUNT(*) anzahl
           FROM rel_kuka  
           GROUP BY kunden_id) kampagnen
  ON a.kunden_id = kampagnen.kunden_id
LEFT JOIN (SELECT kunden_id, COUNT(*) anzahl
           FROM einkommen  e
           WHERE von = (SELECT MAX(von)
                        FROM einkommen 
                        WHERE kunden_id = e.kunden_id)
           GROUP BY kunden_id) ekjahr
  ON a.kunden_id = ekjahr.kunden_id
WHERE a.von = (SELECT MAX(von)
               FROM familienstand
               WHERE kunden_id = a.kunden_id)
GROUP BY a.kunden_id, a.familienstand, 
         kinder.anzahl, kampagnen.anzahl, ekjahr.anzahl

Gruss
Thorsten