Re: Selecting only ONCE from multiple tables

[Bruce Feist]

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