Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1140
  • Last Modified:

mySQL SELECT DISTINCT MATCH

Gday Guys,

I have the following query which works fine

            SELECT
                  DISTINCT MATCH
                        (job_title, job_blurb, job_facility, business_name)
                  AGAINST
                        ('$string' IN BOOLEAN MODE)
                  AS
                        score, job_title, job_blurb, job_facility, business_name
            FROM
                  jobs LEFT JOIN businesses ON jobs.business_id = businesses.business_id
            WHERE
                  MATCH
                        (job_title, job_blurb, job_facility, business_name)                  
                  AGAINST
                        ('$string' IN BOOLEAN MODE)
            ORDER BY
                  score
            DESC

However I want to select all fields from the jobs table, but only want the title, blurb, facility, and business name to be used in the match, Can anybody help out?

Sincerely
-- Zemond
0
zemond
Asked:
zemond
  • 7
  • 5
1 Solution
 
fcardinauxCommented:
Where is the "score" supposed to come from? Is it a number of same titles, blurbs, facilities and businesses? Or is it a specific field? Of which table?
0
 
zemondAuthor Commented:
Score is the generated result of the 'Match Against' statement which returns the amount of times said record is found, I could of equally named is 'occurrences' for more semantic meaning.
0
 
Kevin CrossChief Technology OfficerCommented:
If you want rollup, then the columnlist will have to be unique or you can do aggregates or subqueries on columns that you must choose of the record values for.
SELECT COUNT(*) AS score, /* list out all jobs columns here that you want */
FROM jobs LEFT JOIN businesses ON jobs.business_id = businesses.business_id
WHERE MATCH (job_title, job_blurb, job_facility, business_name) 
              AGAINST ('$string' IN BOOLEAN MODE) 
GROUP BY /* list out all jobs columns here that you want */
ORDER BY score DESC

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
zemondAuthor Commented:
Gday mwvisa1

Firstly thx for the reply....

"If you want rollup, then the columnlist will have to be unique or you can do aggregates or subqueries on columns that you must choose of the record values for."
-- Way to advanced for me... rollup? aggregates? subqueries (i sorted get that one)...

So anyway, I tried your solution and seemed to work alright however the 'score' is always 1. Thus ORDER BY score becomes pointless and the results are not shown in a relevant order.

I know I can just append all my required fields to the several places on my code to get them, but that would be a huge performance hit after a while yeah???

Just to ensure I didnt make some simple mistake with your solution, here is the code I used.

            SELECT COUNT(*) AS score,       jobs.job_id,
                                                      jobs.job_title,
                                                      jobs.job_blurb,
                                                      jobs.job_facility,
                                                      jobs.job_entry_date,
                                                      businesses.business_name                                                
            FROM
                  jobs LEFT JOIN businesses ON jobs.business_id = businesses.business_id
            WHERE
                  MATCH
                        (job_title, job_blurb, job_facility, business_name)                  
                  AGAINST
                        ('$string' IN BOOLEAN MODE)
            GROUP BY
                  jobs.job_id,
                  jobs.job_title,
                  jobs.job_blurb,
                  jobs.job_facility,
                  jobs.job_entry_date,      
                  businesses.business_name                                                
            ORDER BY
                  score
            DESC

Sincerely,
-- Zemond
0
 
Kevin CrossChief Technology OfficerCommented:
What I was trying to say about the aggregates was that I was worried that the added columns may be too unique that you will not get a rollup on the main columns.  By rollup, I mean where you are getting score equal to the occurrences of those values together versus 1 each time since each row is unique.  Out of the fields you have above, the suspect field to be unique on each row is the job_entry_date.  I may be wrong, but if that is the only field that differs then this will fix the problem.

Max / Min are aggregates.  I am going with Max which is last entry typically.
SELECT COUNT(*) AS score,       jobs.job_id, 
                                                      jobs.job_title, 
                                                      jobs.job_blurb, 
                                                      jobs.job_facility, 
                                                      max(jobs.job_entry_date) as max_job_entry_date,
                                                      businesses.business_name                                                
            FROM 
                  jobs LEFT JOIN businesses ON jobs.business_id = businesses.business_id
            WHERE 
                  MATCH
                        (job_title, job_blurb, job_facility, business_name)                  
                  AGAINST 
                        ('$string' IN BOOLEAN MODE) 
            GROUP BY
                  jobs.job_id, 
                  jobs.job_title, 
                  jobs.job_blurb, 
                  jobs.job_facility,   
businesses.business_name                                                
            ORDER BY 
                  score 
            DESC

Open in new window

0
 
zemondAuthor Commented:
Gday mwvisa,

Unfortunately the score remains 1, the query does find all the correct results thou (if that helps), job_entry_date is indeed unique but equally isn't job_id?

Do you have any other ideas?

Sincerely,
-- Zemond
0
 
Kevin CrossChief Technology OfficerCommented:
Please give same of results currently coming back.
0
 
Kevin CrossChief Technology OfficerCommented:
And confirm which columns you would like the occurrences to be counted over.
0
 
zemondAuthor Commented:
Gday mate,

If you would like to view what im working on visit

http://placements.hca-online.com/home.php?option=jobs&view=search

and try this search string

-- agency nursing australian

The first number shown in bold represents the 'score'

I wish to count the occurrences of the string within the following character columns

jobs.job_title,
jobs.job_blurb,
jobs.job_facility

so if it occurs once in title, once is blurb and twice in facility the score should be 4

So if you use the link provided and the search query provided the 'score' should theoretically be the count of highlighted text

Let me know if you need anything else

Sincerely,
-- Zemond
0
 
zemondAuthor Commented:
I don't know how this effects it ( I must removed 'DISTINCT' from the SELECT... but this works

            SELECT *,
                  MATCH(job_title, job_blurb, job_facility)
                  AGAINST
                        ('$string' IN BOOLEAN MODE)
                  AS
                        score
            FROM
                  jobs
            WHERE
                  MATCH
                        (job_title, job_blurb, job_facility)                  
                  AGAINST
                        ('$string' IN BOOLEAN MODE)
            ORDER BY
                  score
            DESC
0
 
zemondAuthor Commented:
Thx for your time, much appreciated. Have a good one.

Sincerely,
-- Zemond
0
 
Kevin CrossChief Technology OfficerCommented:
Ok, glad you found that.

Now that I understood that the occurrence was within same record instead of over rows of same data makes sense.  I was trying for the latter which when I look at the website there are no duplicates, so would always be 1. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now