Selecting only ONCE from multiple tables
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. Hal - 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
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
Re: Selecting only ONCE from multiple tables
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
Re: Selecting only ONCE from multiple tables
Actually, I just found a tutorial on how to mimic the UNION statement with MySQL 3.x: http://www.nstep.net/~mpbailey/programming/tutorials.union.php - Original Message - From: Brian McCain [EMAIL PROTECTED] To: mysQL List [EMAIL PROTECTED] 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 - 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
