MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Hal Vaughan Date:March 18 2003 7:37pm
Subject:Selecting only ONCE from multiple tables
View as plain text  
I'm just getting used to SQL/MySQL, so there is likely a name for this or it 
may be well known -- I just haven't either come across it, or haven't made 
the associations between all the parts yet.

I have 2 tables, one a temp table, and they have the same columns.  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.)

Instead of getting one list with 3 columns, this list iterates through each 
row in Temp once for each row in Cases and also includs the duplicated rows a 
2nd time.  While I expect the duplicated rows to show up twice, how do I 
produce a combined list.

Another way to put it is that I have 2 tables w/ similar columns and want to 
select from the 2 of them and take the results and either output it or put it 
into a new table.

Thanks for any suggestions or help.

Selecting only ONCE from multiple tablesHal Vaughan18 Mar
  • Re: Selecting only ONCE from multiple tablesBruce Feist18 Mar
  • Re: Selecting only ONCE from multiple tablesBrian McCain18 Mar
  • Re: Selecting only ONCE from multiple tablesBrian McCain18 Mar