Joing two tables in SQL with Different Primary Keys

Sherlan Emmanuel Burgonia 86 Reputation points
2022-09-21T07:47:34.377+00:00

Hi,

I'm trying to join two tables, they are both string datatype but the only difference is the first table having an extension number either '001', '002', or '003', etc. I was thinking to use LIKE % but I'm not sure how. I have like thousands of rows but I'll just trimmed it down for example. Attached is the reference table.

243365-image.png

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,232 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 19,906 Reputation points Microsoft Vendor
    2022-09-21T09:26:29.49+00:00

    Hi @Sherlan Emmanuel Burgonia
    Have you validate the answers in your previous thread?
    SQL: Query that finds the ID from a string
    SQL Query that looks for ID number
    If these two issues has been solved, please don't forget to accept the answers if they helped. So other users with similar problem could see this easier. :)

    Back to the current issue,

    I was thinking to use LIKE % but I'm not sure how.

    I don't think LIKE is a good choice to join these two tables.
    You could use string functions to ignore first three digits, as i answered on your previous thread. Like this:

    select *  
    from table1 t1 join table2 t2 on RIGHT(t1.ID,LEN(t1.ID)-3)=t2.ID  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


3 additional answers

Sort by: Most helpful
  1. Olaf Helper 38,711 Reputation points
    2022-09-21T07:57:42.907+00:00

    When they are different in values, then they are different; so what do you want to join here?

    I guess you mean the first 3 digits?
    Example 3124: What if they here exists?
    0013124
    0023124
    0033124
    How to join them?

    0 comments No comments

  2. Sherlan Emmanuel Burgonia 86 Reputation points
    2022-09-21T07:59:23.58+00:00

    yes I meant the first three digits.


  3. Olaf Helper 38,711 Reputation points
    2022-09-21T10:26:59.373+00:00

    You have trash data and expect pure gold as result; that's not really possible.

    A close solution could be

    SELECT *  
    FROM table1 AS T1  
         INNER JOIN  
         table2 AS T2  
             ON T1.ID LIKE '[0-9][0-9][0-9]' + T2.ID  
    

    = patter match on first 3 characters if they are numerics; but I still would expect data failures.

    0 comments No comments