Solved

mySQL SELECT DISTINCT MATCH

Posted on 2008-10-06
13
1,101 Views
Last Modified: 2010-04-21
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
Comment
Question by:zemond
  • 7
  • 5
13 Comments
 
LVL 8

Expert Comment

by:fcardinaux
ID: 22656381
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
 
LVL 9

Author Comment

by:zemond
ID: 22656411
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22656488
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
 
LVL 9

Author Comment

by:zemond
ID: 22656577
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22656599
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
 
LVL 9

Author Comment

by:zemond
ID: 22656645
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22656653
Please give same of results currently coming back.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22656655
And confirm which columns you would like the occurrences to be counted over.
0
 
LVL 9

Author Comment

by:zemond
ID: 22656684
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
 
LVL 9

Author Comment

by:zemond
ID: 22656721
0
 
LVL 9

Author Comment

by:zemond
ID: 22656823
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
 
LVL 9

Author Closing Comment

by:zemond
ID: 31503697
Thx for your time, much appreciated. Have a good one.

Sincerely,
-- Zemond
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22657811
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now