0
  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!

thevoipman
  • 1,773
  • 2
  • 17
  • 44
  • 2
    How is the problem is related to the title of the post? – Tchoupi Aug 03 '12 at 18:03
  • Also I don't any `strip()` method in MySQL. You are probably looking for `substring()` http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring. And `IN()` expects a list, not a string. – Tchoupi Aug 03 '12 at 18:09
  • hi, thank you for pointing out my title The issue I have here is that I have about 50 rows in the `students_playing` table... i don't know how to match/include all of them to filter in my select – thevoipman Aug 03 '12 at 18:12

1 Answers1

3

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 )
)
dancriel
  • 86
  • 4
  • OMG! Thank you so much! I had no idea this was even possible with a one liner query! Thank you so much! You have made my day! – thevoipman Aug 03 '12 at 18:22