findeing matching names from one database to another

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  February 4, 2019 at 3:10 pm

  #381343

  Is there a way of writing a query that lists only matching names from one database to another both on the same SQL Server instance?

  SELECT LTRIM(p.FirstName + ' ' + p.Lastname) as 'Name'
FROM
BCC_DB..People P
INNER JOIN
BCC_DB..Certs C
ON P.PeopleID = C.PeopleID
WHERE C.certStatusID = 1
order by name

  SELECT LTRIM(p.FirstName + ' ' + p.Lastname) as 'Name'
FROM
GCDF_DB..People P
INNER JOIN
GCDF_DB..Certs C
ON P.PeopleID = C.PeopleID
WHERE C.statusid = 2
order by name

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  February 4, 2019 at 3:23 pm

  #2021092

  If I understand correctly, this is what you want:

  
SELECT
    BCC.FirstName + ' ' + BCC.Lastname AS Matching_Name
FROM (
    SELECT p.FirstName, p.Lastname
    FROM
    BCC_DB..People P
    INNER JOIN
    BCC_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.certStatusID = 1
) AS BCC
INNER JOIN (
    SELECT p.FirstName, p.Lastname
    FROM
    GCDF_DB..People P
    INNER JOIN
    GCDF_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.statusid = 2
) AS GCDF ON GCDF.FirstName = BCC.FirstName AND GCDF.Lastname = BCC.Lastname

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  February 5, 2019 at 7:14 am

  #2021146

  ScottPletcher - Monday, February 4, 2019 3:23 PM

  If I understand correctly, this is what you want:

  
SELECT
    BCC.FirstName + ' ' + BCC.Lastname AS Matching_Name
FROM (
    SELECT p.FirstName, p.Lastname
    FROM
    BCC_DB..People P
    INNER JOIN
    BCC_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.certStatusID = 1
) AS BCC
INNER JOIN (
    SELECT p.FirstName, p.Lastname
    FROM
    GCDF_DB..People P
    INNER JOIN
    GCDF_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.statusid = 2
) AS GCDF ON GCDF.FirstName = BCC.FirstName AND GCDF.Lastname = BCC.Lastname

  The logic makes sense and works perfectly! Thanks!

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  February 5, 2019 at 7:24 am

  #2021148

  briancampbellmcad - Tuesday, February 5, 2019 7:14 AM

  ScottPletcher - Monday, February 4, 2019 3:23 PM

  If I understand correctly, this is what you want:

  
SELECT
    BCC.FirstName + ' ' + BCC.Lastname AS Matching_Name
FROM (
    SELECT p.FirstName, p.Lastname
    FROM
    BCC_DB..People P
    INNER JOIN
    BCC_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.certStatusID = 1
) AS BCC
INNER JOIN (
    SELECT p.FirstName, p.Lastname
    FROM
    GCDF_DB..People P
    INNER JOIN
    GCDF_DB..Certs C
    ON P.PeopleID = C.PeopleID
    WHERE C.statusid = 2
) AS GCDF ON GCDF.FirstName = BCC.FirstName AND GCDF.Lastname = BCC.Lastname

  The logic makes sense and works perfectly! Thanks!

  Just a bit of a caveat... if this is anything other than a one off, consider making a synonym to the table in the "other" database so that you can stick to the 2 part naming convention.  If something changes with the name or location of the "other" database, it's much easier to change a synonym than to find out where you need to make changes in code that uses the 3 part naming convention.

  Also, never use the dbname..objectname format for 3 part naming.  ALWAYS use all 3 parts explicitly because you just never know when someone is going to create an identically named object in a different schema nor what the current user defaults for schemaname will be.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)