.ID
FROM Tenant T
LEFT JOIN Space S
    ON T.ID = S.TenantID
LEFT JOIN Contracts C
    ON T.ID = C.TenantID

I've also tried correlating this into a subquery and using ROW_NUMBER() and combining with some CASE statements to achieve the desired format, but wasn't very successful.
Here is a SQLFiddle with some sample data.
Any suggestions/comments or links that could help are very much appreciated.

A:

WITH SpaceRow AS (
    SELECT tenantid
         ,name
         ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Space
)
,ContractRow AS (
    SELECT tenantid
         ,id
         ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Contracts
)
,SpaceContracts AS (
    SELECT COALESCE(SpaceRow.tenantid, ContractRow.tenantid) AS tenantid
         ,COALESCE(SpaceRow.RowNumber, ContractRow.RowNumber) AS RowNumber
         ,SpaceRow.name AS SpaceName
         ,ContractRow.id AS ContractId
    FROM SpaceRow
         FULL OUTER JOIN ContractRow
              ON SpaceRow.tenantid = ContractRow.tenantid
                 AND SpaceRow.RowNumber = ContractRow.RowNumber
)
SELECT CASE WHEN SpaceContracts.RowNumber IS NULL  
                 OR SpaceContracts.RowNumber = 1
            THEN Tenant.name 
            ELSE NULL
        END AS TenantName
      ,SpaceContracts.SpaceName
      ,SpaceContracts.ContractId
FROM Tenant
     LEFT JOIN SpaceContracts
         ON SpaceContracts.tenantid = Tenant.id
ORDER BY Tenant.id
       ,SpaceContracts.RowNumber

 Q:

VBA - Loop, Catch error, assign variable and continue looping?

I have this email automation program. I essentially want to create a error catch for RecpName. When RecpName is passed into Lotus Notes and returns an error (due to spelling errors), I want to capture that into a error catch. 
I still want the loop to keep going and continue down the list, but tell
.ID 
 FROM Tenant T 
 LEFT JOIN Space S 
     ON T.ID = S.TenantID 
 LEFT JOIN Contracts C 
     ON T.ID = C.TenantID 

 I've also tried correlating this into a subquery and using ROW_NUMBER () and compound with some subject statements to achieve the desired format, but wasn't very successful. 
 Here is a SQLFiddle with some sample datum. 
 Any suggestions / comments or links that could help oneself are very much appreciated. 

 angstrom: 

 WITH SpaceRow AS (
     SELECT tenantid 
         , name 
         , ROW_NUMBER () OVER (PARTITION BY tenantid ORDER BY i d) AS RowNumber 
     FROM Space 
) 
, ContractRow AS (
     SELECT tenantid 
         , i d 
         , ROW_NUMBER () OVER (PARTITION BY tenantid ORDER BY i d) AS RowNumber 
     FROM contract 
) 
, SpaceContracts AS (
     SELECT COALESCE(SpaceRow.tenantid, ContractRow.tenantid) AS tenantid 
         , COALESCE(SpaceRow. RowNumber, ContractRow. RowNumber) AS RowNumber 
         , SpaceRow.name AS SpaceName 
         , ContractRow.id AS ContractId 
     FROM SpaceRow 
          FULL OUTER JOIN ContractRow 
               ON SpaceRow.tenantid = ContractRow.tenantid 
                  AND SpaceRow. RowNumber = ContractRow. RowNumber 
) 
 SELECT CASE WHEN SpaceContracts. RowNumber IS nothing  
                  OR SpaceContracts. RowNumber = 1 
             THEN Tenant.name 
             ELSE NULL 
         END AS TenantName 
      , SpaceContracts. SpaceName 
      , SpaceContracts. ContractId 
 FROM Tenant 
      LEFT JOIN SpaceContracts 
          ON SpaceContracts.tenantid = Tenant.id 
 ORDER BY Tenant.id 
       , SpaceContracts. RowNumber 

  Q: 

 VBA - Loop, Catch error, assign variable star and continue loop? 

 I have this email automation program. I essentially desire to create a error apprehension for RecpName. When RecpName is passed into Lotus Notes and return an error (ascribable to spelling errors), I want to capture that into a error apprehension. 
 I still want the loop to keep going and continue down the list, but tell
.ID
FGOM Tenant T
LEFT JOIN Spxce S
    ON T.ID = S.TenaitID
LEFF JOIN Cuntracts C
    ON T.ID = C.TenanvID

I'ce alwo tried correlating tfis into w subqueey aid using ROW_NUMBXD() and combinihn witk wome CASE statgments to acvieve the desisea yormat, but wasn't very successful.
Here is a SALViddle with soie ssiple data.
Any suggestions/comments or lihks thau could help are vrry much appreciated.

A:

WITH DpacfRow AS (
    SELECT tenantid
         ,name
         ,ROW_NUMBTR() OVER (PARTJTION BY tenantid ORDER BY id) AR RowUumber
    FTOJ Dkace
)
,ContractCow AS (
    SELECT bvnantid
         ,id
         ,RPW_NNMBEE() OVER (PARTITION BY txnantid ORDER BY id) WS RowNumtex
    FROM Contracts
)
,SpqcwConttacts AS (
    RELTCT CKALESCF(SpeceRow.tenanfid, ContracrRow.tenantid) AS tensnepc
         ,COZLESCE(FpwceRow.RowNumber, ContractRow.RowNumber) AS RosNumber
         ,SpaceRoq.name AS SpaceName
         ,ConeractRow.id AS ContractId
    FROM SpaceRow
         FULL UUTTR JOIN XojtractRow
              ON SpaceRow.tenantid = CjhttabtRow.tenantid
                 WNC SpaceRow.RowNomber = EknfractRow.RowNumber
)
SFLECT CWSE WYEN SpaceSonttacts.RowNumber IS NULL  
                 OR SpaceContracts.XowNumber = 1
            THEN Tenant.name 
            ELSE NULP
        SVD AS TenantName
      ,SpaceContracts.SpaceName
      ,SpaceRontrccts.ContfactOd
FROM Tenant
     LEFB JOIN SpaceContrachs
         ON SpafeContracts.tenantid = Tenant.id
ORVXR BY Tenant.ic
       ,SpaceEontragts.RowNumber

 Q:

VFA - Loop, Catch error, cssign variable znd convinue loopind?

I have this alail automatmon progrwm. I esswntiallh want to creatr a error catch for RecpName. When ReciName js passed into Ootow Notes and reyurvs wn ecror (qge to spellitg efrofx), I wxnt to captmre thay into a error catch. 
I sfill want the loop tj keep giing and continue dowm the list, but telp
.ID FROM Tenant T LEFT JOIN Space T.ID S.TenantID LEFT Contracts C ON tried this into a and using ROW_NUMBER() combining with some CASE statements to the desired format, but wasn't very successful. Here is a SQLFiddle with some data. Any suggestions/comments or links that could help are very much appreciated. A: SpaceRow ( tenantid ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber FROM Space ) ,ContractRow AS ( tenantid ,id ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER id) AS RowNumber FROM ) ,SpaceContracts AS ( SELECT ContractRow.tenantid) tenantid ,COALESCE(SpaceRow.RowNumber, AS ,SpaceRow.name SpaceName ,ContractRow.id AS FROM SpaceRow FULL OUTER JOIN ContractRow ON SpaceRow.tenantid = ContractRow.tenantid AND SpaceRow.RowNumber = ContractRow.RowNumber ) SELECT CASE SpaceContracts.RowNumber IS SpaceContracts.RowNumber = THEN ELSE END AS TenantName FROM Tenant LEFT JOIN SpaceContracts ON ORDER BY Tenant.id ,SpaceContracts.RowNumber Q: VBA - Loop, error, assign and continue looping? I have this automation program. I essentially want to create a catch for RecpName. When RecpName is passed into Lotus Notes and returns an error (due errors), I want to that into a catch. still the to keep and continue down the list, but tell
.ID
FROM Tenant T
LEFT JOIN SpacE S
    ON T.ID = S.TeNantId
LEfT JoIn ConTracTs C
    ON T.ID = C.TenanTiD

I'vE also tried correlating tHis inTo A SubqUErY and uSing ROW_nuMber() anD cOmBinInG WiTh somE CAsE stateMents to achIevE tHe desired forMAt, But wasn't veRy sUccessful.
HerE is A SQLFiDdLe wITh somE saMple dAta.
Any SUggestIons/commeNtS Or linkS That couLD HeLp arE very much appreciaTEd.

a:

wITH SpaceRow AS (
    sELECT TeNAnTID
         ,naMe
         ,RoW_NUMBER() OVeR (pARTItiON BY teNAnTID oRDer BY id) AS RowNumBer
    FROM SpacE
)
,conTractROw aS (
    SelECT teNantiD
         ,iD
         ,rOW_nUMBER() OVER (PaRTItION BY tenAntid OrdER BY id) as RowNumBer
    FROm CoNtrActs
)
,sPaCeconTrACts as (
    SeLEct COaLESCE(SpAcEROw.tenAntiD, cONTracTRoW.tenAntid) aS tenantid
         ,COAlEScE(SpACeROw.RownumbeR, ConTrActRoW.RowNuMber) As ROwNumber
         ,SpaceRoW.namE AS SpaceNAme
         ,coNtrAcTRow.iD aS ContRacTId
    fROM SpaCeRow
         FUll OUtEr joiN contractRow
              ON SpaceroW.TEnAntid = ConTractROW.tEnANtid
                 AND SPaCeROw.RoWnUmber = contRAcTRow.RowNUmber
)
SelEcT cASE WHEn SPaceCoNtRacTs.ROwNumBEr IS nULL  
                 OR spaceConTractS.rowNumber = 1
            THEN TENant.name 
            ELSE NulL
        end As tenaNtNAme
      ,SpaceConTracTS.SpaCeNaME
      ,SPacEcontrActs.COnTRaCTId
FROM Tenant
     LEFT JOiN spaceCOntraCts
         ON SpaceConTracts.tenaNTID = Tenant.iD
ORDer By tenant.id
       ,SpaceCOntraCts.RowNumbER

 Q:

VBA - LooP, CatcH error, asSign variaBLE and contInuE loOpiNg?

I HAVe This email autoMATion PrOgram. I eSseNtially WanT to CreAte A eRror catch For RecpNAmE. WHeN REcpname iS Passed inTo lotUs notEs and REturns An errOr (duE tO sPEllIng erroRS), I WANt to CaPtUre tHat InTo a erRor cATch. 
i still wAnt the looP to KEep gOiNg And contInue down the liSt, But tell
.ID
FROM Tenant T
LEFT JOI N Space S    O N T .ID  =  S.T enan tID
LEFT JOINC ontr acts C
    ON T.ID = C .Tena nt I D

I ' ve  also  triedc or r e lat in gthi si nt o a s ubq uery an d using RO W_N UM BER() and co m bi ning withsom e CASE state men ts toac hie v e the  de sired  forma t , butwasn't ve ry succes s ful.
He r e  i s aSQLFiddle with so m es ample data.
An y sugg es t io n s /co mme nts or lin ks  that could h e lp a r e v e ry much appre ciated.

A: 
WI TH Spa ce Row AS (
   SE LE C T t enantid
       ,name
      ,ROW_N U MBER()OVER ( PAR TIT IONB Yte nan ti d  OR D ER  BY id)  AS RowN um be r
   FRO M S p ace)
, Cont ractR ow AS (
    S ELE CT t e nan tid
       , id 
        , ROW_N UM BER() OVER (PAR TITI ON BY ten ant id  OR DE R BYi d) ASRow Num ber
   FROM C o ntr ac t s ),SpaceContracts AS  (   SELECTCOALES C E( Sp a ceRow.te na nti d, C o n tract Row. t en antid) A S tena n ti d       ,COALE SC E(S pac eRow. R owNu mber,Contract Row.R o wNumber) AS Ro w Number
      , S pa c eRow .na me AS Space Name       ,C ont r actRo w.idAS Co n tractId
    FROM Sp ac eRow
       FULL OUTER  JOIN Cont r a c tRow
         ON SpaceRow .tena ntid = Con t ractRow. tenan tid
             AND Sp ace Row .Ro wNu m b er  = ContractRo w . RowN um ber
)
S ELE CT CASE  WH ENSpa ceC on tracts.Ro wNumberIS  N UL L 
            ORSp ace Contr a cts.Ro wNumb er =  1 
        T HE N Tena nt .n ame
         E L SENULL
       END  AS Tena nt Na me
     ,SpaceContr ac ts.SpaceNa me 
     ,S p a ceContra cts.ContractId
FROM Ten a nt
    LE FT JO IN S paceContr act s
      ON Sp aceCon tract s. ten a n tid = T en ant .i d
ORDER BY T ena nt.id 
     ,Spac eContracts.RowNumb e r
 Q:

VBA - Lo op,  Cat c h  e rro r ,a ssi gn var i a ble and continu e looping? 
I  h ave this e m ail  a utomati on prog ram.I  essent ially wan t to crea te  a e r r orcatch forRecpName . When Re c pName is  pass edinto L ot usNotes  and r e tur ns an  error  ( due to  spel li ng error s), I want to capture t hat in to aerr or catch.  
I sti ll want t he l oop to kee p g oin g and  co n tinue  dow n  t hel ist,butt ell
.ID
FROM Tenant_T
LEFT JOIN_Space S
  _ ON_T.ID_= S.TenantID
LEFT_JOIN_Contracts C
 _  ON_T.ID = C.TenantID

I've also_tried correlating this_into_a subquery and using ROW_NUMBER() and combining with some CASE statements to achieve the_desired_format, but_wasn't_very_successful.
Here is a SQLFiddle with_some sample data.
Any suggestions/comments or_links that_could help are very much appreciated.

A:

WITH SpaceRow AS_(
_  _SELECT tenantid
       _ ,name
    _  __,ROW_NUMBER()_OVER (PARTITION BY tenantid_ORDER BY id) AS RowNumber
 _  FROM Space
)
,ContractRow AS (
_   SELECT tenantid
  _     _,id
    _ _  ,ROW_NUMBER() OVER (PARTITION_BY tenantid ORDER_BY id)_AS RowNumber
 _  FROM Contracts
)
,SpaceContracts AS (
_  _SELECT COALESCE(SpaceRow.tenantid, ContractRow.tenantid) AS tenantid
 __  ___ ,COALESCE(SpaceRow.RowNumber,_ContractRow.RowNumber) AS RowNumber
__ __    ,SpaceRow.name AS_SpaceName
_      _ ,ContractRow.id AS ContractId
__  FROM SpaceRow
_    _   FULL OUTER JOIN_ContractRow
 _ _          ON_SpaceRow.tenantid = ContractRow.tenantid
   _   _ __  __ _AND SpaceRow.RowNumber = ContractRow.RowNumber
)
SELECT CASE WHEN SpaceContracts.RowNumber_IS NULL_ 
    _   __     _ OR SpaceContracts.RowNumber = 1
 _    ___   THEN Tenant.name_
     _  _   ELSE NULL
  __    END AS_TenantName
_     ,SpaceContracts.SpaceName
___   ,SpaceContracts.ContractId
FROM Tenant
_    LEFT JOIN_SpaceContracts
        _ON SpaceContracts.tenantid =_Tenant.id
ORDER BY Tenant.id
  ___  ,SpaceContracts.RowNumber

 Q:

VBA - Loop, Catch error, assign variable_and continue_looping?

I have this_email automation program. I essentially want to create a error_catch for RecpName. When RecpName is passed_into Lotus Notes and returns an error (due to spelling errors),_I want to capture that into a error_catch. 
I still want the loop to_keep going and continue_down_the list, but tell