How to do a ranking/relevance search with both varchar(fulltext) & int fields in one query

I am trying to write a search query that will return results with a ranking/relevance value to use as the display order.  For example, I have a lastname field, a firstname field, and a studentId field.  I want to allow the user to type in one or more fields and display ranked results.  Even if one field they type in does not match this should just make the ranking lower and not exclude the record (this works because of the union). In the code below I am able to sort by relevance, but I don't know how to add the search of my studentId (int) field into the query with a valid relevance.  I know that I cannot use fulltext on an int field.  Is there anyway to make this work?  Any help is appreciated. Thanks!

example data:
studentId  lastName  firstName
123          james       barb
434          roberts     john

If the user searches on lastName = james, studentId = '434', firstName = 'john' the results should be

john roberts (2 fields matched)
barb james (only 1 field matched)
SELECT 		
	studentId, 
	lastName, 
	firstName, 
	MATCH(lastName) AGAINST('james' IN BOOLEAN MODE) as relevance FROM students 
WHERE MATCH(lastName) AGAINST('james' IN BOOLEAN MODE) having relevance > 0.2
		
UNION DISTINCT
		
SELECT 		
	studentId, 
	lastName, 
	firstName, 
MATCH(firstName) AGAINST('john' IN BOOLEAN MODE) as relevance FROM students 
WHERE MATCH(firstName) AGAINST('john' IN BOOLEAN MODE) having relevance > 0.2
 
ORDER BY relevance DESC

Open in new window

farmingtonisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

racekCommented:

SELECT          
        studentId, 
        lastName, 
        firstName, 
        IF(lastName='Nowak',1,0)+IF(FirstName='james',1,0) AS relev_value
FROM  yourtable
ORDER BY relev_value DESC
                

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
racekCommented:
maybe you can do it this way:
SELECT studentId, lastName, firstName  
FROM  yourtable  
WHERE  lastName='roberts'+ FirstName='john'+ studentId =434 > 0
ORDER BY lastName='roberts'+ FirstName='john'+ studentId =434 DESC
 
 
 
     

Open in new window

0
farmingtonisAuthor Commented:
Part of the reason I was using match was to be able to do partial searches.  I tried both above and the first works I think but I'm not sure how to get out the "0" matches.  The 2nd should take care of that problem, but I can't get the query to work by changing the "=" to like 'roberts%'
Thanks for the help this is way better than doing a bunch of unions!
0
racekCommented:
and what about
SELECT          
        studentId, 
        lastName, 
        firstName, 
        MATCH(lastName) AGAINST('james' IN BOOLEAN MODE)
      + MATCH(firstName) AGAINST('barb' IN BOOLEAN MODE)
      + studentId = 434 > 0 as relevance 
 
FROM students 
ORDER BY relevance DESC
                

Open in new window

0
farmingtonisAuthor Commented:
I ended up adding a where clause with the same If's in the select statement with a > 0 so I only get back ones that match.  Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.