RE: LEFT JOIN trouble. Please help.

2001-12-13 Thread Jn Fedorek 



-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

LEFT JOIN trouble. Please help.

2001-12-12 Thread Jn Fedorek 

Hi,
 I've got this problem:

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

Regards
John.


-
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

Re: LEFT JOIN trouble. Please help.

2001-12-12 Thread Aleksandar Bradaric 

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