Solved

mySQL SELECT DISTINCT MATCH

Posted on 2008-10-06
13
1,105 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

785 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