"not unique table/alias"

[Jack Tanner]

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;

Not unique table/alias: 't1'.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;
Works fine! (But this query is different, because it doesn't get

the rows that are NULL in one of the tables.)

Using MySQL 4.1.1. Thanks in advance for your help.

[Siemel Naran]

"Jack Tanner" <ih**@hotmail.com> wrote in message

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;

Not unique table/alias: 't1'.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;
Works fine! (But this query is different, because it doesn't get

the rows that are NULL in one of the tables.)

Using MySQL 4.1.1. Thanks in advance for your help.

Looks like a bug.

[Siemel Naran]

"Jack Tanner" <ih**@hotmail.com> wrote in message

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;

Not unique table/alias: 't1'.
SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;
Works fine! (But this query is different, because it doesn't get

the rows that are NULL in one of the tables.)

Using MySQL 4.1.1. Thanks in advance for your help.

Looks like a bug.

[Jack Tanner]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message news:<5T*********************@bgtnsc05-news.ops.worldnet.att.net>...

"Jack Tanner" <ih**@hotmail.com> wrote in message

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;

>> Not unique table/alias: 't1'.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;

>> Works fine! (But this query is different, because it doesn't get

the rows that are NULL in one of the tables.)

Using MySQL 4.1.1. Thanks in advance for your help.

Looks like a bug.

Could someone from @mysql.com confirm this? Should I be reporting on bugs.mysql.com?

[Jack Tanner]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message news:<5T*********************@bgtnsc05-news.ops.worldnet.att.net>...

"Jack Tanner" <ih**@hotmail.com> wrote in message

I have two complex subqueries that I need to join. I suspect this
problem is due to using aliases instead of table names, but I don't
know how to work around it (temporary tables?). Please help.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
JOIN t1 ON t2.col = t1.col;

>> Not unique table/alias: 't1'.

SELECT col FROM (subquery-1) AS t1, (subquery-2) AS t2
WHERE t2.col = t1.col;

>> Works fine! (But this query is different, because it doesn't get

the rows that are NULL in one of the tables.)

Using MySQL 4.1.1. Thanks in advance for your help.

Looks like a bug.

Could someone from @mysql.com confirm this? Should I be reporting on bugs.mysql.com?

[Aggro]

Jack Tanner wrote:

Not unique table/alias: 't1'.

I don't know if this helps you, but I have always done it differently.
See the examples:

mysql> select * from t1,t2 left join t1 on t1.id=t2.id;
ERROR 1066: Not unique table/alias: 't1'

mysql> select * from t1 left join t2 on t1.id=t2.id;
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | car | 1 | cat |
| 2 | cube | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

# Or if you want to use aliases:
mysql> select * from t1 as k1 left join t2 as k2 on k1.id=k2.id;

I have version 3 of MySQL so can't try with subqueries. And I don't
whether you are supposed to be able to do it the way you tried or not.

[Aggro]

Jack Tanner wrote:

Not unique table/alias: 't1'.

I don't know if this helps you, but I have always done it differently.
See the examples:

mysql> select * from t1,t2 left join t1 on t1.id=t2.id;
ERROR 1066: Not unique table/alias: 't1'

mysql> select * from t1 left join t2 on t1.id=t2.id;
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | car | 1 | cat |
| 2 | cube | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

# Or if you want to use aliases:
mysql> select * from t1 as k1 left join t2 as k2 on k1.id=k2.id;

I have version 3 of MySQL so can't try with subqueries. And I don't
whether you are supposed to be able to do it the way you tried or not.