[search] Mysql query with relational tables

djavet · #1 (**permalink**) 09-22-05, 10:32

Hello

I've a little Mysql query problem.
I've 2 tables for a address book application.

Code:

CREATE TABLE `contacts` (
  `pid` int(11) NOT NULL auto_increment,
  `organisation` varchar(50) NOT NULL default '',
  `title` varchar(50) default NULL,
  `nom` varchar(50) default NULL,
  `prenom` varchar(50) default NULL,
  `address1` varchar(150) default NULL,
  `address2` varchar(150) default NULL,
  `npa` varchar(10) default NULL,
  `city` varchar(50) default NULL,
  `code_pays_rel` varchar(50) NOT NULL default '',
  `phone` varchar(25) default NULL,
  `fax` varchar(25) default NULL,
  `mobile` varchar(25) default NULL,
  `email` varchar(70) default NULL,
  `web_url` varchar(70) default NULL,
  `web_text` varchar(70) default NULL,
  `comment` blob,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1126 ;

And a country table with 236 entries:

Code:

CREATE TABLE `pays` (
  `idpays` int(11) NOT NULL auto_increment,
  `code_pays` varchar(50) default NULL,
  `pays` varchar(50) default NULL,
  PRIMARY KEY  (`idpays`),
  UNIQUE KEY `code_pays` (`code_pays`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=237 ;

When I search with a keyword only in contact. No problem (simple!):

Code:

SELECT *
FROM contacts
WHERE nom like '%SearchString%' or 
web_url like '%SearchString%' or 
organisation like '%SearchString%'
ORDER BY organisation, nom ASC

Result: 1 (and this is correct)

but if I add the "pays" table to add the name of the country for my contact name regarding the "code_pays_rel" field in contact table. I've 236 entries instead of 1 ;o(

Code:

SELECT *
FROM contacts, pays
WHERE nom like '%SearchString%' or 
web_url like '%SearchString%' or 
organisation like '%SearchString%' or 
pays like '%SearchString%' AND 
contacts.code_pays_rel=pays.code_pays
ORDER BY pays, organisation, nom ASC

Result: 236 !!!

What I am doing wrong?

A lot of thx for your help and time.

Regards,
Dominique Javet

tombell · #2 (**permalink**) 09-22-05, 10:34

are there any columns relating the two tables?

r937 · #3 (**permalink**) 09-22-05, 10:36

Quote:

Originally Posted by djavet

What I am doing wrong?

you are mixing ANDs and ORs without the proper parentheses

try this --

Code:

  FROM contacts
     , pays
 WHERE (
       nom like '%SearchString%' 
    or web_url like '%SearchString%' 
    or organisation like '%SearchString%' 
    or pays like '%SearchString%' 
       )
   AND contacts.code_pays_rel=pays.code_pays

alternatively, and this is the recommended approach, use this syntax and the problem does not appear:

Code:

  FROM contacts
inner
  join pays
    on contacts.code_pays_rel=pays.code_pays
 WHERE nom like '%SearchString%' 
    or web_url like '%SearchString%' 
    or organisation like '%SearchString%' 
    or pays like '%SearchString%'