[search] Mysql query with relational tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[search] Mysql query with relational tables

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2005-09-22 : 10:26:21

Hello

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

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:

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!):

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(

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 10:28:07

>>I've a little Mysql query problem.

This is SQL Server Forum
Better to post MySQL forum of www.Dbforums.com

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 10:30:57

Anyway it seems that you didnt join those two tables by keycolumn

Madhivanan

Failing to plan is Planning to fail

djavet
Starting Member

36 Posts

Posted - 2005-09-22 : 10:35:18

Thx, I will go their.

Regards, Dom

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 10:39:19

You need to bracket your ORs and ANDs to explicitly describe what you mean from the JOIN - does MySQL support JOIN syntax, rather than the old fashioned WHERE Table1.Col1 = Table2.Col2? That would make the statement's intent a bit more obvious/readable

Kristen

djavet
Starting Member

36 Posts

Posted - 2005-09-22 : 10:41:28

Thx It's working with inner join.
I learn everydax ;o)

Dominique

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources