Search code examples
mysqlmatchingstripping

stripping when selecting

  1. I have two tables, ones lists students_playing but it's about 10 characters long, first and last name combined....
  2. I want to select from the avail_students but I don't want to select the students that has the same name that matches the first 5 characters.

My select command right now is:

select * from avail_students where lastplayed<now() and name not IN strip(students_playing),5)

I know the last part is not correct but I don't even know where to start.

Any kind of help I get from this is greatly appreciated. Thank you in advance!

Solution

  • I think the LEFT function and nested subqueries are what you are looking for.

    The example below should compare the first 5 characters only.

    SELECT * FROM avail_students 
WHERE lastplayed < now() 
AND NOT EXISTS (
    SELECT * FROM students_playing
    WHERE LEFT( students_playing.name, 5 ) = LEFT( avail_students.name, 5 )
)