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
