Selecting identical rows from 2 tables (basically Row AND Row)
I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! 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 identical rows from 2 tables (basically Row AND Row)
Do you have two identical tables? If not, then doing a select the way you propose won't yield any rows. If they are identical, then you've already got the data, and so wouldn't need to select anything. I'm sure I'm misunderstanding exactly what you're trying to do, so maybe you could explain it a bit further. In any case, if you've got a unique key that tied the two tables together (or if you could set it up so that you did), that would be your best bet. Using all those values in your where clause is going to slow your selects down considerably if there aren't keys on them (and if the number of columns is variable, it would probably be difficult to have the keys to handle all combinations of columns). Brian McCain - Original Message - From: Hal Vaughan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:29 PM Subject: Selecting identical rows from 2 tables (basically Row AND Row) I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! 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 - 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 identical rows from 2 tables (basically Row AND Row)
On Friday 21 March 2003 05:42 pm, Brian McCain wrote: Do you have two identical tables? If not, then doing a select the way you propose won't yield any rows. If they are identical, then you've already got the data, and so wouldn't need to select anything. I'm sure I'm misunderstanding exactly what you're trying to do, so maybe you could explain it a bit further. I have a table where different people will select items. The items Person1 selects will go into Table1 (described as TestCases below), the items Person2 selects go in Table2 (described as TestTemp below). After Person1 and Person2 have each selected items, I want to be able to look at which items they have BOTH selected and list only those items -- only the rows selected by both. Hal remainder of correspondence follows... In any case, if you've got a unique key that tied the two tables together (or if you could set it up so that you did), that would be your best bet. Using all those values in your where clause is going to slow your selects down considerably if there aren't keys on them (and if the number of columns is variable, it would probably be difficult to have the keys to handle all combinations of columns). Brian McCain - Original Message - From: Hal Vaughan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:29 PM Subject: Selecting identical rows from 2 tables (basically Row AND Row) I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! 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 - 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
