[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Left join w/o on or using join_condition
From:       Bill Adams <badams () tqs ! com>
Date:       2001-10-31 19:16:19
[Download RAW message or body]

Rick Emery wrote:

> Try going with simply:
> SELECT  T1.*, T2.* FROM tbl1 T1, tbl2 T2
> WHERE    T1.a=1AND T1.b=2 AND T2.a=2 AND T2.b=T1.b;

Well, if I resort to that then the documentation is wrong.

Here is what I am really trying to do.  Say you have a table and data:

DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl1 ( a int, c char, val int );

INSERT INTO tbl1 VALUES
 ( 1, 'R', 10 ),
 ( 2, 'R', 11 ),
 ( 3, 'R', 12 ),
 ( 1, 'T', 20 ),
 ( 3, 'T', 21 )
;


Here is a ugly, self-join select that gives me the output that I want:

SELECT T1.*, T2.*
FROM tbl1 T1 LEFT JOIN tbl1 T2 ON( T1.a=T2.a
AND ( T1.c<>T2.c OR ( T1.c='R' AND T2.c='T' )))
WHERE T1.c='R'
;

+------+------+------+------+------+------+
| a    | c    | val  | a    | c    | val  |
+------+------+------+------+------+------+
|    1 | R    |   10 |    1 | T    |   20 |
|    2 | R    |   11 | NULL | NULL | NULL |
|    3 | R    |   12 |    3 | T    |   21 |
+------+------+------+------+------+------+
3 rows in set (0.00 sec)


What I really want is a simpler select like informix allows, e.g.:
SELECT T1.*, T2 FROM tbl1 T1, OUTER tbl1 T2
WHERE T1.a=T2.a AND T1.c='R' AND T2.c='T';

My real query is much worse than this.

Any suggestions?

--Bill


>
>
> -----Original Message-----
> From: Bill Adams [mailto:badams@tqs.com]
> Sent: Wednesday, October 31, 2001 12:21 PM
> To: Mysql List
> Subject: Left join w/o on or using join_condition
>
> My mind is not working with me this morning...
>
> According to the manual about join syntax:
> http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOI
> N
>
> One should be able to do a LEFT JOIN without a
> join_condition, eg. an "ON" or a "USING":
> > table_reference LEFT [OUTER] JOIN
> table_reference
>
> However a query like:
>
> SELECT  T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2
> WHERE    T1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b;
>
> Gives me a  "ERROR 1064: You have an error in your
> SQL syntax near 'WHERE..."
>
> What am I doing wrong?  You used to be able to do
> that.  I know the conditional can be moved into a
> ON( ... ) but I do not want to do that for
> compatibility with other DBs.
>
> MySQL 3.23.41
>
> --Bill
>
> ---------------------------------------------------------------------
> 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 <mysql-thread89634@lists.mysql.com>
> To unsubscribe, e-mail <mysql-unsubscribe-remery=excel.com@lists.mysql.com>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




---------------------------------------------------------------------
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 <mysql-thread89650@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=progressive-comp.com@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[prev in list] [next in list] [prev in thread] [next in thread]