need help with a CTE

• DaveBriCam

  SSCarpal Tunnel

  Points: 4294

  More actions

  April 20, 2018 at 9:49 am

  #403282

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 20, 2018 at 10:13 am

  #1987701

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 20, 2018 at 10:41 am

  #1987710

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  Just remember that as the data volume increases you may reach a tipping point and have to revisit the code.  When testing, you may want to consider setting up a million row test data set to see how it performs.

• DaveBriCam

  SSCarpal Tunnel

  Points: 4294

  More actions

  April 20, 2018 at 12:02 pm

  #1987730

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 20, 2018 at 12:57 pm

  #1987742

  briancampbellmcad - Friday, April 20, 2018 12:02 PM

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

  You need to surround the column names with square brackets since you are using special characters in the names.

• DaveBriCam

  SSCarpal Tunnel

  Points: 4294

  More actions

  April 20, 2018 at 1:13 pm

  #1987748

  Lynn Pettis - Friday, April 20, 2018 12:57 PM

  briancampbellmcad - Friday, April 20, 2018 12:02 PM

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

  You need to surround the column names with square brackets since you are using special characters in the names.

  I'm getting a "Conversion failed when converting the nvarchar value 'James Malone' to data type int." somewhere (says line 1, but.... )

  WITH

  TMH_CTE (peopleId, Name, [DCC#], State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS [DCC#] ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, [DCC#]) AS
(SELECT p.peopleId AS [DCC#], p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.State, DCC_CTE.[DCC#]
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 20, 2018 at 1:24 pm

  #1987750

  briancampbellmcad - Friday, April 20, 2018 1:13 PM

  Lynn Pettis - Friday, April 20, 2018 12:57 PM

  briancampbellmcad - Friday, April 20, 2018 12:02 PM

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

  You need to surround the column names with square brackets since you are using special characters in the names.

  I'm getting a "Conversion failed when converting the nvarchar value 'James Malone' to data type int." somewhere (says line 1, but.... )

  WITH

  TMH_CTE (peopleId, Name, [DCC#], State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS [DCC#] ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, [DCC#]) AS
(SELECT p.peopleId AS [DCC#], p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.State, DCC_CTE.[DCC#]
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#]

  Not enough information, can't see what you see.  Please post the DDL for the table(s) involved.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 20, 2018 at 1:25 pm

  #1987751

  briancampbellmcad - Friday, April 20, 2018 1:13 PM

  Lynn Pettis - Friday, April 20, 2018 12:57 PM

  briancampbellmcad - Friday, April 20, 2018 12:02 PM

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

  You need to surround the column names with square brackets since you are using special characters in the names.

  I'm getting a "Conversion failed when converting the nvarchar value 'James Malone' to data type int." somewhere (says line 1, but.... )

  WITH

  TMH_CTE (peopleId, Name, [DCC#], State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS [DCC#] ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, [DCC#]) AS
(SELECT p.peopleId AS [DCC#], p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.State, DCC_CTE.[DCC#]
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#]

  The problem is that your column aliases in the query are not in the same order as the column alias names in the CTE declaration, and as you only really need the column aliases in the query itself, and you provide them, you don't need to waste the space on them in the CTE declaration.   Here's your query updated to include brackets on the State column because that is a reserved word in T-SQL, and while not needed, it;s usually a good idea to avoid confusion:WITH TMH_CTE AS (

      SELECT    p.peopleId,
            p.FirstName + ' ' + p.LastName AS Name,
            p.DCCnumber AS [DCC#],
            s.stateAbbrveation AS [State]
    FROM TMHP_DB..People AS p
        LEFT OUTER JOIN TMHP_DB..PeopleContactInfo AS c
            ON p.peopleid = c.peopleid
        LEFT OUTER JOIN TMHP_DB..states AS s
            ON c.stateId = s.stateId
),
    DCC_CTE AS (

          SELECT    p.peopleId AS [DCC#],
                p.FirstName + ' ' + p.LastName AS Name
        FROM DCC_DB..People AS p
)
SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.[State] DCC_CTE.[DCC#]
FROM TMH_CTE
    INNER JOIN DCC_CTE
        ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#];

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• DaveBriCam

  SSCarpal Tunnel

  Points: 4294

  More actions

  April 20, 2018 at 1:28 pm

  #1987753

  Thanks... that makes sense and works!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 20, 2018 at 1:29 pm

  #1987755

  sgmunson - Friday, April 20, 2018 1:25 PM

  briancampbellmcad - Friday, April 20, 2018 1:13 PM

  Lynn Pettis - Friday, April 20, 2018 12:57 PM

  briancampbellmcad - Friday, April 20, 2018 12:02 PM

  sgmunson - Friday, April 20, 2018 10:13 AM

  briancampbellmcad - Friday, April 20, 2018 9:49 AM

  I have a query below that is working fine, but I'm needing to connect it to another database within the same instance to do a loose comparison of last names from a concatenation and getting this type of data returned:
  peopleId    tempflag    Name    BC#    State
  18    0    Khoi Bishop    1904    AL
  11    0    Jennifer Baldwin    2392    GA

  SELECT p.peopleId,
   p.tempflag,
     p.FirstName + ' ' + p.LastName AS Name
  ,p.BCnumber AS BC#
     ,s.stateAbbr AS State
FROM MHP_DB..People p
   LEFT OUTER JOIN
MHP_DB..PeopleContactInfo c
  ON p.peopleid = c.peopleid
   LEFT OUTER JOIN
MHP_DB..states s
  ON c.stateId = s.stateId
order by s.stateAbbrv

  I want to compare the resulting to a database that also has a name concatenated and a DCC# to see if the numbers match to give me something like:
  peopleId    tempflag    Name    BC#1    BC#2  State
  18    0    Khoi Bishop    1904    1904  AL
  11    0    Jennifer Bald    2392   2392 GA
  Can I use a common table expression to do this? Thanks!

  Yes, although it isn't necessary to the task.   Depending on the number of rows you get from each contributing table combination, the query may perform better if each source of data is queried into a temp table, and those temp tables indexed, then the final select joins the two temp tables.   If the volume is small and will stay that way, you can have two CTEs, one for each source of data, and then join them in the final SELECT.   Testing is the only way to know what you're up against.   Try it and see....

  It's a small amount of data so I'm trying the CTE but it looks like my logic or syntax is wrong:

  WITH

  TMH_CTE (peopleId, Name, DCC#, State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS DCC# ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, DCC#) AS
(SELECT p.peopleId AS DCC#, p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.DCC#, TMH_CTE.State, DCC_CTE.DCC#
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.DCC# = DCC_CTE.DCC#

  You need to surround the column names with square brackets since you are using special characters in the names.

  I'm getting a "Conversion failed when converting the nvarchar value 'James Malone' to data type int." somewhere (says line 1, but.... )

  WITH

  TMH_CTE (peopleId, Name, [DCC#], State) AS
(SELECT p.peopleId, p.FirstName + ' ' + p.LastName AS Name ,p.DCCnumber AS [DCC#] ,s.stateAbbrveation AS State
FROM TMHP_DB..People p LEFT OUTER JOIN TMHP_DB..PeopleContactInfo c ON p.peopleid = c.peopleid
         LEFT OUTER JOIN TMHP_DB..states s ON c.stateId = s.stateId),

  DCC_CTE (Name, [DCC#]) AS
(SELECT p.peopleId AS [DCC#], p.FirstName + ' ' + p.LastName AS Name
FROM DCC_DB..People p)

  SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.State, DCC_CTE.[DCC#]
FROM TMH_CTE
JOIN
DCC_CTE
ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#]

  The problem is that your column aliases in the query are not in the same order as the column alias names in the CTE declaration, and as you only really need the column aliases in the query itself, and you provide them, you don't need to waste the space on them in the CTE declaration.   Here's your query updated to include brackets on the State column because that is a reserved word in T-SQL, and while not needed, it;s usually a good idea to avoid confusion:WITH TMH_CTE AS (

      SELECT    p.peopleId,
            p.FirstName + ' ' + p.LastName AS Name,
            p.DCCnumber AS [DCC#],
            s.stateAbbrveation AS [State]
    FROM TMHP_DB..People AS p
        LEFT OUTER JOIN TMHP_DB..PeopleContactInfo AS c
            ON p.peopleid = c.peopleid
        LEFT OUTER JOIN TMHP_DB..states AS s
            ON c.stateId = s.stateId
),
    DCC_CTE AS (

          SELECT    p.peopleId AS [DCC#],
                p.FirstName + ' ' + p.LastName AS Name
        FROM DCC_DB..People AS p
)
SELECT TMH_CTE.peopleId, TMH_CTE.Name, TMH_CTE.[DCC#], TMH_CTE.[State] DCC_CTE.[DCC#]
FROM TMH_CTE
    INNER JOIN DCC_CTE
        ON TMH_CTE.[DCC#] = DCC_CTE.[DCC#];

  May not need to include them but it isn't wrong either.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 20, 2018 at 1:40 pm

  #1987759

  briancampbellmcad - Friday, April 20, 2018 1:28 PM

  Thanks... that makes sense and works!

  Your welcome.   Enjoy! 🙂

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)