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