Hi all,
I was wondering if someone could tell me whether the following SQL would
have to perform two searches,
One to get the score in the SELECT clause and one in the WHERE clause to
only return matches?
If it does perform two searches, would it be best to put WHERE score != 0 in
the WHERE clause instead?
SELECT N.noteIdentity
, LEFT( N.note, 80 ) AS note
, MATCH (N.note) AGAINST ('<cfqueryparam value="#form.searchString#"
cfsqltype="CF_SQL_LONGVARCHAR" />' IN BOOLEAN MODE ) AS score
, CN.candidateIdentity
, AN.addressIdentity
, CN_.clientIdentity
, CN__.contactIdentity
, EN.entityIdentity
, JN.jobIdentity
FROM db_au_com_exclaimit.tbl_note N
INNER JOIN db_au_com_exclaimit.tbl_user U
ON N.ownerIdentity = U.userIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_candidate_note CN
ON N.noteIdentity = CN.noteIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_address_note AN
ON N.noteIdentity = AN.noteIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_client_note CN_
ON N.noteIdentity = CN_.noteIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_contact_note CN__
ON N.noteIdentity = CN__.noteIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_entity_note EN
ON N.noteIdentity = EN.noteIdentity
LEFT OUTER JOIN db_au_com_exclaimit.tbl_job_note JN
ON N.noteIdentity = JN.noteIdentity
WHERE MATCH (N.note) AGAINST ('<cfqueryparam
value="#form.searchString#" cfsqltype="CF_SQL_LONGVARCHAR" />' IN BOOLEAN
MODE )
ORDER BY score DESC;
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox <http://www.pacificfox.com.au/> http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .
*
Web Design and Development
*
SMS Solutions, including developer API
*
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!
