UNION is new in MySQL 4. Be careful of that. http://www.mysql.com/doc/en/UNION.html
If you don't have MySQL 4, your problem becomes a bit tricky, because MySQL doesn't know that T.Amount and C.Amount are conceptually the same, so it won't group the columns. Basically, you want to select T.* if T.Amount > 500 and C.* if C.Amount > 500. Which, without UNION, is only possible through separate queries, unless I'm missing something. Brian McCain ----- Original Message ----- From: "Bruce Feist" <[EMAIL PROTECTED]> To: "mysQL List" <[EMAIL PROTECTED]> Sent: Tuesday, March 18, 2003 11:49 AM Subject: Re: Selecting only ONCE from multiple tables > Hal Vaughan wrote: > > >I'd like to be able to select from both tables and get one listing. > > > >Table 1 is Cases, Table 2 is Temp. They have columns Name, Amount, Zip. > > > >SELECT * FROM Cases AS C, Temp AS T WHERE (C.Amount > 500 OR T.Amount > 500); > > > >produces a list of 38 rows w/ 6 columns (the first 3 columns from Cases, the > >2nd 3 columns from Temp). This should select 2 rows from Temp and 4 from > >Cases. (The 2 rows in Temp are duplicates of the ones in Temp.) > > > > > You're doing a join (more accurately, what's called a Cartesian Product) > in the above SQL... it's designed to look at combinations of information > from each of two tables, and combine them to create a new table with > individual rows containing data from each. Instead, you need what's > called a "union". Since you want to preserve duplicates, you need the > extra keyword ALL. Try this: > > Select * from Cases C WHERE C.Amount > 500 > UNION ALL > Select * from Temp T WHERE T.Amount > 500; > > Warning -- my main expertise is with other RDBMSs, and this syntax might > be incorrect for MySql. > > Bruce Feist > > > > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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
