You can do what you asked for by making a temporary table, as follows. create temporary table T (this varchar(..), that varchar(..)); insert into T select tbl1.this, "no record" from tbl1 left join tbl2 on tbl1.id = tbl2.id where tbl2.id is null; insert into T select tbl1.this, tbl2.that from tbl1, tbl2 where tbl1.id = tbl2.id; insert into T select "no record", tbl2.that from tbl2 left join tbl1 on tbl2.id = tbl1.id where tbl1.id is null;
With a UNION, you can do it all in one SQL statement, but unions are on the "to do someday" list for MySQL. You probably can't do it in MySQL without a temporary table or some procedural programming. Hope this helps a little. Date: Tue, 05 Mar 2002 13:56:34 -0500 From: "Forer, Leif" <[EMAIL PROTECTED]> Subject: SELECT this IF that To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Message-id: <[EMAIL PROTECTED]> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1 I want to select and join data from two tables if the same id exists in both tables. If the id only exists in one table I want MySQL to return a string like "sorry, no records found". How can I do this? I'm guessing it's something like: mysql> SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id = tbl2.id, return the data, "no record"); (Obviously that's not a real query). --------------------------------------------------------------------- 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
