mySQL SELECT DISTINCT MATCH

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?

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.

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

Select all Open in new window

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

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

Please give same of results currently coming back.

And confirm which columns you would like the occurrences to be counted over.

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

You can visit the working query here
http://placements.hca-online.com/home.php?option=jobs2&view=search&search_string=nursing%20agency%20australian

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

Thx for your time, much appreciated. Have a good one.

Sincerely,
-- Zemond

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. :)