-Original Message-
From: Aleksandar Bradaric [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 12, 2001 3:32 PM
To: Jn 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