0

I routinely get in testing files in CSV/Excel format from standardized testing services with individual students first and last names in separate columns. My sop is to go through each student listed and match them up with students in my database to assign each row in the csv/Excel the student's student number. This is a time consuming process, so I wanted to let SQL do all the heavy lifting for me.

The idea is to create a temporary table with four fields, studentID and studentName to be populated from the existing student table in the database and the first and last names from the CSV/Excel file. Then, I can have SQL look up the first and last name from the CSV file and populate the studentNumber and StudentName fields in the temporary table so I can download the table and go through the list much quicker.

I can get the temporary table created and populate the firstName and lastName from the CSV/Excel file, but I'm having problems pulling the data in from the excising students table.

Any help would be appreciated.

CREATE TEMPORARY TABLE processing(
    studentID int(11),
    studentName varchar(50),
    firstName varchar(20),
    lastName varchar (30)
);

INSERT INTO processing (firstName, lastName) VALUES ('John', 'Doe');
INSERT INTO processing (firstName, lastName) VALUES ('Evelyn', 'Smith');

UPDATE processing p 
INNER JOIN students s   
ON (s.studentName like '%p.firstName%' and s.studentName like '%p.lastName%')
SET p.studentID=s.studentID, p.studentName=s.studentName;


SELECT * FROM `processing` WHERE 1;

When the code is run, the firstName and lastName are there, but I get NULLs for the studentID and the StudentName.

enter image description here

Phil
  • 157,677
  • 23
  • 242
  • 245
Erick Molnar
  • 11
  • 2
  • 2
    I think you want `LIKE CONCAT('%', p.firstName, '%')`, etc – Phil Jul 17 '23 at 23:13
  • Thanks, but not really, the full name in the database contains middle initials and suffixes, it's just the way the data is set up in the SIS. By asking for a %first Name% AND a %last Name% match it increases the odds that a good match is found. – Erick Molnar Jul 18 '23 at 01:50
  • 1
    The commas in Phil's comment and the commas in the duplicate question linked, are not what I think you think they do. The commas in the `CONCAT` function are just to separate the arguments that the function takes. `CONCAT('%', p.firstName, '%')` for the firstName John, for example, evaluates to `'%John%'`; what you are currently using is trying to find the word `p.firstName`, not the value `John` in John Doe, `p.firstName` does not exist in `John Doe` therefore you are not getting matches. – Isaac Rene Jul 18 '23 at 02:24
  • Thanks, Isaac. I see what you are getting at. Tried it and didn't work (got an error) but I think this puts me closer to a solution... – Erick Molnar Jul 18 '23 at 02:37
  • Isaac, thank you, that actually did work like a charm! problem solved! – Erick Molnar Jul 18 '23 at 02:43

0 Answers0