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.