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:[EMAIL PROTECTED]] > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php