[EMAIL PROTECTED] wrote:
Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/26/2005 02:08:50 PM:
Hi list,
I have a query like this (the original is very big and names of
columns/tables are in portuguese):
SELECT * FROM tbl1 t JOIN tbl2 u ON (t.id_tbl2=u.id)
For a schema like this:
CREATE TABLE tbl2 (
id INT NOT NULL auto_increment PRIMARY KEY,
data_col2 int NOT NULL
) ENGINE=InnoDB;
CREATE TABLE tbl1 (
id INT NOT NULL auto_increment PRIMARY KEY,
id_tbl2 int NULL,
data_col1 int NOT NULL,
INDEX (id_tbl2),
FOREIGN KEY (id_tbl2)
REFERENCES tbl2(id) ON UPDATE CASCADE
) ENGINE=InnoDB;
but i have NULL values in t.id_tbl2.
Here is some data:
mysql> INSERT INTO tbl2 VALUES (NULL, 11),(NULL, 12);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tbl2;
+----+-----------+
| id | data_col2 |
+----+-----------+
| 5 | 11 |
| 6 | 12 |
+----+-----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tbl1 VALUES (NULL, 1, 123),(NULL, 2, 456), (NULL,
NULL, 789);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tbl1;
+----+---------+-----------+
| id | id_tbl2 | data_col1 |
+----+---------+-----------+
| 1 | 1 | 123 |
| 2 | 2 | 456 |
| 3 | NULL | 789 |
+----+---------+-----------+
3 rows in set (0.00 sec)
The above query gives:
mysql> SELECT t.id, t.data_col1, u.data_col2 FROM tbl1 t JOIN tbl2 u ON
(t.id_tbl2=u.id);
+----+-----------+-----------+
| id | data_col1 | data_col2 |
+----+-----------+-----------+
| 1 | 123 | 11 |
| 2 | 456 | 12 |
+----+-----------+-----------+
2 rows in set (0.01 sec)
But should be
+----+-----------+-----------+
| id | data_col1 | data_col2 |
+----+-----------+-----------+
| 1 | 123 | 11 |
| 2 | 456 | 12 |
| 3 | 789 | NULL |
+----+-----------+-----------+
Any sugestion?
--
Nuno Pereira
Change your INNER JOIN to a LEFT JOIN and all will be as you wanted.
SELECT * FROM tbl1 t LEFT JOIN tbl2 u ON (t.id_tbl2=u.id)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
This solved the problem to this test (bad) case, but not to real
situation. Monday I will place the full test.
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
