Sorry guys, I forgot to post to the list as well..
-----Original Message-----
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 5:42 PM
To: [EMAIL PROTECTED]
Subject: RE: Query problem
Hi Schalk,
I recommend using parenthesis to group your AND/OR logic more firmly.
Assuming you want all the fields for documents that have content matching
'demyer Padgham robinson' with a document type of 'Motion' that refer to
either the California State court OR the district Circuit Court: Federal,
California.
Example:
SELECT *
FROM documents
WHERE
MATCH (content) AGAINST ('demyer Padgham robinson')
AND doctype = 'Motion'
AND ( jstate = 'California: State Court'
OR
jdistrict = 'Circuit Court: Federal, California' );
It is also good practise to specify precisely the fields you are interested
in rather than just *. It may be easier to write the query this way, but
it's going to slow everything down for you.
As an aside, looking at your table structure, depending on your database
size you may want to consider normalisation. For more info check out:
http://www.databasejournal.com/sqletc/article.php/1428511
Also, it might be interesting for you to know that fixed width tables are a
lot faster than dynamic or variable width ones (like the one you've
provided).. For more info you can check out:
http://dev.mysql.com/doc/mysql/en/MyISAM_table_formats.html
I hope this helps,
Regards,
Lachlan
-----Original Message-----
From: Schalk Neethling [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 5:19 PM
To: [EMAIL PROTECTED]
Subject: RE: Query problem
Hey there
I have the following table structure:
CREATE TABLE documents (
id int(11) NOT NULL auto_increment,
user varchar(50) NOT NULL default '',
olduser varchar(50) NOT NULL default '',
username varchar(100) NOT NULL default '',
uploaddate timestamp(14) NOT NULL,
docdate varchar(100) NOT NULL default '',
docno varchar(255) NOT NULL default '',
title varchar(150) NOT NULL default '',
summary varchar(255) NOT NULL default '',
content text NOT NULL,
doctype varchar(80) NOT NULL default '',
docuri varchar(255) NOT NULL default '',
vjudge varchar(100) NOT NULL default '',
vexpert varchar(100) NOT NULL default '',
vspeciality varchar(150) NOT NULL default '',
didiversity varchar(100) NOT NULL default '',
dicity varchar(80) NOT NULL default '',
didiversitybar varchar(80) NOT NULL default '',
dilawfirm varchar(200) NOT NULL default '',
jstate varchar(100) NOT NULL default '',
jdistrict varchar(100) NOT NULL default '',
jappellate varchar(100) NOT NULL default '',
keywords varchar(255) NOT NULL default '',
PRIMARY KEY (id),
FULLTEXT KEY content (content)
) TYPE=MyISAM;
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am going
wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should be
found. How do I go about ensuring that only documents that matches the
doctype is returned and no other documents.
Thanks for any help on this.
--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
