-----Original Message-----
From: Aleksandar Bradaric [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 12, 2001 3:32 PM
To: Ján Fedorek
Cc: MySQL List
Subject: Re: LEFT JOIN trouble. Please help.
Hi,
> I have two tables and I want create third one with LEFT JOIN
> First table table1 containts field AA as a primary key (AA is NOT NULL).
> Second table table2 containts field AA as a primary key (AA is NOT NULL)
> too.
> Ex:
> CREATE TABLE a (PRIMARY KEY(AA))
> SELECT table1.AA FROM table2
> LEFT JOIN table1 ON table2.AA=table1.AA
> WHERE ...;
> The problem is, that mysql creates new table with field AA, but it's not
NOT
> NULL (it's allow NULL) => I cannot create primary key on this field.
> Is it bug or I'm wrong ???
Your nulls are created by LEFT JOIN (it returns null if
there is no matching values for table2.AA in table1).Maybe
you should try:
CREATE TABLE a (PRIMARY KEY(AA))
SELECT table1.AA FROM table2, table1
WHERE table2.AA=table1.AA AND ...;
or:
CREATE TABLE a (PRIMARY KEY(AA))
SELECT table1.AA FROM table2
LEFT JOIN table1 ON table2.AA=table1.AA
WHERE table1.AA IS NOT NULL AND ...;
Regards,
Sasa
Hi,
That's correct, LEFT JOIN must return NULL if there's no matching values for
table1.AA=table2.AA but the type of returned field table1.AA is not usable
for PRIMARY KEY because it's accepting NULL values (it's not NOT NULL in
create definition).
Create definition of table1 is:
CREATE TABLE table1 (AA VARCHAR(32) NOT NULL, ...)
but LEFT JOIN return this:
CREATE TABLE table1 (AA VARCHAR(32), ...) <<<< there's no NOT NULL
Don't you why ?
JohnF.
---------------------------------------------------------------------
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
