Thanks ! It's exactlly what I wanted. I got the same result a few seconds ago using EMS MySQL Manager but I didn't understand how. Your example is much better !
What is the difference between a LEFT JOIN and a LEFT OUTER JOIN ? And of course you're right for the data type ; Thanks again A 15:16 07/12/2001 -0500, vous avez écrit : >mysql> create table users( > -> usrID smallint unsigned auto_increment, > -> usrZip char(5), > -> primary key(usrID)); >Query OK, 0 rows affected (0.00 sec) > >mysql> create table cities( > -> ctyZip char(5) not null, > -> ctyName varchar(30) not null, > -> primary key(ctyZip)); >Query OK, 0 rows affected (0.00 sec) > >[Insert some values] > >mysql> select * from users; >+-------+--------+ >| usrID | usrZip | >+-------+--------+ >| 1 | 90210 | >| 2 | 12345 | >| 3 | 30300 | >| 4 | NULL | >+-------+--------+ >4 rows in set (0.00 sec) > >mysql> select * from cities; >+--------+---------------+ >| ctyZip | ctyName | >+--------+---------------+ >| 90210 | Beverly Hills | >| 12345 | Fubar Town | >| 30300 | Rowland City | >+--------+---------------+ >3 rows in set (0.00 sec) > >mysql> select usrID, usrZip, ctyName from users left join cities on >usrZip=ctyZip; >+-------+--------+---------------+ >| usrID | usrZip | ctyName >+-------+--------+---------------+ >| 1 | 90210 | Beverly Hills | >| 2 | 12345 | Fubar Town | >| 3 | 30300 | Rowland City | >| 4 | NULL | NULL | >+-------+--------+---------------+ >4 rows in set (0.00 sec) > >mysql> select usrID, usrZip, ctyName from users left join cities on >usrZip=ctyZip where usrID=4; >+-------+--------+---------------+ >| usrID | usrZip | ctyName >+-------+--------+---------------+ >| 4 | NULL | NULL | >+-------+--------+---------------+ >1 row in set (0.00 sec) > >Is that what you are looking for? > >sorry I redone your table definitions, but you should have the same type >of data for the linked keys (in thsi case you had varchar255 for zip in >the peoples table and a tinyint for the zip in the city table. > >HTH > >Etienne > > >Michaël Delorme wrote: >> >> Hi guys >> >> I have a problem on a SQL query : I got no records selected. Below >> is a sample of my 2 tables : >> >> **** >> A table identifying people, containing their adress therefore a ZIP >> code. However for some people I don't have their adress, so no ZIP code : >> >> Table People >> +----------+------+ >> | PeopleID | zip | ... >> +----------+------+ >> | 1 | 1 | >> | 2 | Null | >> | 3 | Null | >> | 4 | 3 | >> | ... | >> +----------+------+ >> >> **** >> Another table describe ZIP codes and cities : >> >> Table City >> +-----+------+ >> | zip | city | ... >> +-----+------+ >> | 1 | aaa | >> | 2 | bbb | >> | 3 | ccc | >> | 4 | ddd | >> | 5 | eee | >> | ... | >> +-----+------+ >> >> Here is my query : >> >> SELECT >> people.peopleId, >> people.zip, >> city.city >> >> FROM >> people, >> city >> >> WHERE >> people.zip = city.zip >> and people.peopleId = "2"; >> >> I don't get any selection... wheras I expect : >> +----------+------+------+ >> | PeopleID | zip | city | >> +----------+------+------+ >> | 2 | Null | Null | >> +----------+------+------+ >> >> Where am I wrong ? >> Thanks in advance >> Michael >> >> Here are the table definitions : >> >> CREATE TABLE `people` ( >> `peopleId` tinyint(1) unsigned NOT NULL auto_increment, >> `zip` varchar(255) default NULL, >> PRIMARY KEY (`peopleId`) >> ) >> >> CREATE TABLE `city` ( >> `zip` tinyint(1) unsigned NOT NULL auto_increment, >> `city` varchar(255) default NULL, >> PRIMARY KEY (`zip`) >> ) >> >> __________________________________________ >> Michael Delorme >> GIS Manager >> French Mediterranean Botanical Conservancy >> mailto:[EMAIL PROTECTED] >> >> --------------------------------------------------------------------- >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > >-- >Etienne Marcotte >Specifications Management - Quality Control >Imperial Tobacco Ltd. - Montreal (Qc) Canada >514.932.6161 x.4001 > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
