Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: [search] Mysql query with relational tables

    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

  2. #2
    Join Date
    Jun 2005
    Posts
    23
    are there any columns relating the two tables?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2005
    Posts
    5
    Yope:
    Code:
    contacts.code_pays_rel=pays.code_pays
    Dom

  5. #5
    Join Date
    Jun 2005
    Posts
    23
    Sorry about that.
    Didn't notice it.
    Anyway, Rudy answered your question

  6. #6
    Join Date
    Sep 2005
    Posts
    5
    Excellent! It's working well with the inner join.
    Every day I learn!

    Thx a lot. Dominique

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •