Combining two SQL queries into one tidy table

Question

Let me preface by saying that I am a beginner, so feel free to make the queries look better as you see fit. Anyway, I'm having trouble trying to combine all of my two queries, which I thought would be easier than trying to combine both into one table. I tried a union, but that is only returning columns from the first query. What I want to accomplish is joining the first query with the second by using the TrainProgram. UserLastName is the one column I REALLY need, but it didn't know come through. Any help would be greatly appreciated. Thanks. Here's the first query:

select 
    aa.TrainProgramID
  , aa.TrainProgram_OrganizationalUnitName
  , aa.TrainProgramMetaDataTypeName
  , aa.TrainProgramName
  , a.Activity_Name
  , a.Activity_Duration
  , a.Activity_UpdateDate
  , i.EntityName        as [MPN]
  , a.Activity_TypeName as [Activity_MDT]
  , g.Activity_ID
  , a.Activity_Description
  , a.ActivityPar_SNa  as [ActivitySName]
  , a.Activity_Order
  , a.ActivityPar_SID  as [ActivitySID]
from rawViews.MCD_DH_Activities a
  left join rawViews.MCD_DH_TrainingProgramsBasicData aa
    on a.Activity_EntityID = aa.TrainingProgramID
  inner join dh.MCD_DH_Activities g
    on g.Activity_ID = a.Activity_ID
  full join rawViews.MCD_DH_ActivityPrerequisites h
    on h.ActivityID = a.Activity_ID
  left join dh.MCD_DH_BaseEntities i
    on a.Activity_EntityID = i.EntityID
where a.Activity_VersionStatusID = 1

Here's my second query:

select 
    b.TrainProgramID
  , c.uid
  , c.UserLastName
from dh.MCD_DH_UserTrainProAssoc a
  inner join rawViews.MCD_DH_TrainProgramsBasicData b
    on a.TrainProgramID = b.TrainProgramID
  inner join rawViews.MCD_DH_UBasicData c
    on a.uid = c.uid

[Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3) - Aaron Bertrand](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) — SqlZim, Mar 03 '17 at 19:21

score 2 · Accepted Answer · answered Mar 03 '17 at 19:25

2

I think this is what you are looking for:

select 
    aa.TrainProgramID
  , aa.TrainProgram_OrganizationalUnitName
  , aa.TrainProgramMetaDataTypeName
  , aa.TrainProgramName
  , c.uid
  , c.UserLastName
  , a.Activity_Name
  , a.Activity_Duration
  , a.Activity_UpdateDate
  , i.EntityName        as [MPN]
  , a.Activity_TypeName as [Activity_MDT]
  , g.Activity_ID
  , a.Activity_Description
  , a.ActivityPar_SNa  as [ActivitySName]
  , a.Activity_Order
  , a.ActivityPar_SID  as [ActivitySID]
from rawViews.MCD_DH_Activities a
  left join rawViews.MCD_DH_TrainingProgramsBasicData aa
    on a.Activity_EntityID = aa.TrainingProgramID
  left join dh.MCD_DH_UserTrainProAssoc utpa
    on aa.TrainProgramID = utpa.TrainProgramID
  left join rawViews.MCD_DH_UBasicData c
    on utpa.uid = c.uid
  inner join dh.MCD_DH_Activities g
    on g.Activity_ID = a.Activity_ID
  full join rawViews.MCD_DH_ActivityPrerequisites h
    on h.ActivityID = a.Activity_ID
  left join dh.MCD_DH_BaseEntities i
    on a.Activity_EntityID = i.EntityID
where a.Activity_VersionStatusID = 1

answered Mar 03 '17 at 19:25

SqlZim

37,248
6
41
59

Thank you! That makes sense. :) – BoyGeorge Mar 03 '17 at 20:50
@SQLZim intermixing inner and outer joins like this can cause some data surprises. The left joins before the inner joins will most likely be turned into inner joins. Since G is joining to A, it would be best to make that the first join in the query. from a inner join g on g = a left join aa on a = aa left join utpa on aa = utpa left join c on utpa = c left join i on a = i FULL join h on h = a – Wes H Mar 06 '17 at 14:36
@WesH Your concern is valid in certain situations, but it does not apply to this situation: 1) the query does not use the `force order` hint; 2) the query is not [Forcing Join Order Without Hints - Erik Darling](https://www.brentozar.com/archive/2015/05/forcing-join-order-without-hints/) 3) the join conditions only reference equality between themselves and one other table, and do not mix joins within difference branches of joins from the main table. Please see [ypercubeᵀᴹ's answer to Does the join order matter in SQL?](http://stackoverflow.com/a/9615296/2333499) – SqlZim Mar 06 '17 at 16:18

Combining two SQL queries into one tidy table

1 Answers1