Re: Query and join problem with null values - Michael Delorme - com.mysql.lists.mysql

6 messages in com.mysql.lists.mysqlRe: Query and join problem with null ...

From	Sent On	Attachments
Michaël Delorme	07 Dec 2001 10:59
rick herbel	07 Dec 2001 11:05
sac	07 Dec 2001 11:42
Michael Delorme	07 Dec 2001 11:47
Etienne Marcotte	07 Dec 2001 12:16
Michael Delorme	07 Dec 2001 12:53

Subject:	Re: Query and join problem with null values
From:	Michael Delorme (mich...@delorme.nom.fr)
Date:	12/07/2001 12:53:44 PM
List:	com.mysql.lists.mysql

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

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:delo...@engref.fr

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-emarcott=itl...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-delormemichael=engr...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php