Share via

sql searching problem

nononame2021 256 Reputation points
Sep 13, 2022, 4:42 PM

I have a excel to maintain a customer list with 2 column, 1 is english and another is chinese name,which contains 3

I have another table in database customer table. also with c_english, c_chinese name and customer ID.

I have create a temp table and then import all the data in excel into temp table.

and then match to the physical table customer by using below script.

I want to use temp table as a based to check.how many customer can be matched and find out the customer ID.

select c.cistomerid, temp.english , temp.chimese, c.c_english, c.c_chinese from temp left join customer c on temp.chinese =c.c_chinese

however, the total no. of record is not same as the number of record in temp table, why. (more then 3 record)

I would like to check how many record can be matched by chinese name column, if it can't be match then the customer ID should be null or blank. otherwise , output the customer id

what should I do? any script to provide me for reference.

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

2 answers

Sort by: Newest
  1. LiHongMSFT-4306 30,056 Reputation points
    Sep 14, 2022, 2:38 AM

    Hi @nononame2021
    Try this code:

    SELECT T.english,T.chinese,MAX(C.CustomerID)CustomerID  
    FROM Temp T left join Customer C on T.chinese =C.c_chinese  
    GROUP BY T.english,T.chinese  
    

    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.

    0 comments No comments

  2. Naomi Nosonovsky 8,126 Reputation points
    Sep 13, 2022, 7:05 PM

    Your script is correct, it just means that customer table has same c_chinese values in multiple rows and they all match. Do you want to grab just a single random customer id if there are multiple matches?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.