multi-table select (not a join)
How do you select from two tables, without joining those tables? I just want the results of one table slapped on the end of the results from another table because I don't want the overheard of running two seperate queries. For instance: Lets say that I have these two tables (and thur values): Foo Bar a1 b1 a3b3 c3 a2 a2 a4 b4 c4 I would like a table (resultset) returned something like this. Of course it would need an additional field to specify which table the values came from: a1 b1foo a2 b2foo a3 b3 c3 bar a4 b4 c4 bar Anyone know how to do a query like this? Thanks. Neal - 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: multi-table select (not a join)
Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Thanks. Neal -Original Message- From: Herman Verkade [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 12:14 AM To: 'neal' Subject: RE: multi-table select (not a join) You want to do a UNION. See: http://www.mysql.com/doc/en/UNION.html Regards, Herman Verkade -Original Message- From: neal [mailto:[EMAIL PROTECTED]] Sent: 23 September 2002 07:57 To: mySQL Subject: multi-table select (not a join) How do you select from two tables, without joining those tables? I just want the results of one table slapped on the end of the results from another table because I don't want the overheard of running two seperate queries. For instance: Lets say that I have these two tables (and thur values): Foo Bar a1 b1 a3 b3 c3 a2 a2 a4 b4 c4 I would like a table (resultset) returned something like this. Of course it would need an additional field to specify which table the values came from: a1 b1foo a2 b2foo a3 b3 c3 bar a4 b4 c4 bar Anyone know how to do a query like this? Thanks. Neal - 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: multi-table select (not a join)
At 0:23 -0700 9/23/02, neal wrote: Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! Right. What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Not sure why you'd need multiple *connections*. You can use multiple *queries*, for example like this: CREATE TABLE tmp SELECT ... FROM t1 ... INSERT INTO tmp SELECT ... FROM t2 ... INSERT INTO tmp SELECT ... FROM t3 ... At the end of this, tmp will be the same as if you'd done UNION. More precisely, as if you'd done UNION ALL, because duplicates won't be removed. To remove them, use SELECT DISTINCT when retriving from tmp. Thanks. Neal -Original Message- From: Herman Verkade [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 12:14 AM To: 'neal' Subject: RE: multi-table select (not a join) You want to do a UNION. See: http://www.mysql.com/doc/en/UNION.html Regards, Herman Verkade -Original Message- From: neal [mailto:[EMAIL PROTECTED]] Sent: 23 September 2002 07:57 To: mySQL Subject: multi-table select (not a join) How do you select from two tables, without joining those tables? I just want the results of one table slapped on the end of the results from another table because I don't want the overheard of running two seperate queries. For instance: Lets say that I have these two tables (and thur values): Foo Bar a1 b1 a3b3 c3 a2 a2 a4 b4 c4 I would like a table (resultset) returned something like this. Of course it would need an additional field to specify which table the values came from: a1 b1foo a2 b2foo a3 b3 c3 bar a4 b4 c4 bar Anyone know how to do a query like this? Thanks. Neal - 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: multi-table select (not a join)
Thanks for the suggestion but this would actually create a new table, correct (the first statement that is)? I just want a resultset with these values, without writing to disk. On another note, yeah youre right not a different connection object, but I presume I would need to run two seperate queries and recieve back two seperate resultsets. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 9:21 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 0:23 -0700 9/23/02, neal wrote: Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! Right. What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Not sure why you'd need multiple *connections*. You can use multiple *queries*, for example like this: CREATE TABLE tmp SELECT ... FROM t1 ... INSERT INTO tmp SELECT ... FROM t2 ... INSERT INTO tmp SELECT ... FROM t3 ... At the end of this, tmp will be the same as if you'd done UNION. More precisely, as if you'd done UNION ALL, because duplicates won't be removed. To remove them, use SELECT DISTINCT when retriving from tmp. Thanks. Neal -Original Message- From: Herman Verkade [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 12:14 AM To: 'neal' Subject: RE: multi-table select (not a join) You want to do a UNION. See: http://www.mysql.com/doc/en/UNION.html Regards, Herman Verkade -Original Message- From: neal [mailto:[EMAIL PROTECTED]] Sent: 23 September 2002 07:57 To: mySQL Subject: multi-table select (not a join) How do you select from two tables, without joining those tables? I just want the results of one table slapped on the end of the results from another table because I don't want the overheard of running two seperate queries. For instance: Lets say that I have these two tables (and thur values): Foo Bar a1 b1 a3b3 c3 a2 a2 a4 b4 c4 I would like a table (resultset) returned something like this. Of course it would need an additional field to specify which table the values came from: a1 b1foo a2 b2foo a3 b3 c3 bar a4 b4 c4 bar Anyone know how to do a query like this? Thanks. Neal - 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: multi-table select (not a join)
At 9:55 -0700 9/23/02, neal wrote: Thanks for the suggestion but this would actually create a new table, correct (the first statement that is)? I just want a resultset with these values, without writing to disk. Then you must upgrade to 4.x so that you have UNION support. Either that, or write a client program that issues multiple SELECT statements and buffers the results in memory. What's your objection to creating the new table? Just delete it when you're done with it. On another note, yeah youre right not a different connection object, but I presume I would need to run two seperate queries and recieve back two seperate resultsets. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 9:21 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 0:23 -0700 9/23/02, neal wrote: Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! Right. What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Not sure why you'd need multiple *connections*. You can use multiple *queries*, for example like this: CREATE TABLE tmp SELECT ... FROM t1 ... INSERT INTO tmp SELECT ... FROM t2 ... INSERT INTO tmp SELECT ... FROM t3 ... At the end of this, tmp will be the same as if you'd done UNION. More precisely, as if you'd done UNION ALL, because duplicates won't be removed. To remove them, use SELECT DISTINCT when retriving from tmp. Thanks. Neal - 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: multi-table select (not a join)
I just presume that this will add alot of overhead to the query. The reason I wanted to be able to do something like a union rather than seperate queries is because of overhead. I dunno ... am I wrong? Is it not that bad? Also, I tried the query you suggested ... can you really do this(?): insert into tmp select userId from iteneraries I was getting an error 'near insert into'. It seems you're trying to execute a subquery within a query ... can MySQL do this? Thanks. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 10:09 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 9:55 -0700 9/23/02, neal wrote: Thanks for the suggestion but this would actually create a new table, correct (the first statement that is)? I just want a resultset with these values, without writing to disk. Then you must upgrade to 4.x so that you have UNION support. Either that, or write a client program that issues multiple SELECT statements and buffers the results in memory. What's your objection to creating the new table? Just delete it when you're done with it. On another note, yeah youre right not a different connection object, but I presume I would need to run two seperate queries and recieve back two seperate resultsets. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 9:21 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 0:23 -0700 9/23/02, neal wrote: Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! Right. What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Not sure why you'd need multiple *connections*. You can use multiple *queries*, for example like this: CREATE TABLE tmp SELECT ... FROM t1 ... INSERT INTO tmp SELECT ... FROM t2 ... INSERT INTO tmp SELECT ... FROM t3 ... At the end of this, tmp will be the same as if you'd done UNION. More precisely, as if you'd done UNION ALL, because duplicates won't be removed. To remove them, use SELECT DISTINCT when retriving from tmp. Thanks. Neal - 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: multi-table select (not a join)
On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote: I had a question about the use of the UNION command in this context. The original poster asked about getting the name of the _table_ as well as some other data, which would seem to be relatively necessary for doing many types of things with the results of the query. For example, if you issue a query that gives you the union of seven different tables, and then you want to do another query based on these results, you'll need to know which of the seven tables a particular result came from. The docs on UNION don't seem to address this, none of the responses mentioned it, and I can't seem to find any discussion of how to retrieve the table name in a SELECT query (I acknowledge that most of the time you wouldn't need it, but in a UNION you might). How do you get the table name returned as part of the query results? Or am I misunderstanding how one would work with the results? Jesse Sheidlower - 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: multi-table select (not a join)
At 10:12 -0700 9/23/02, neal wrote: I just presume that this will add alot of overhead to the query. The reason I wanted to be able to do something like a union rather than seperate queries is because of overhead. I dunno ... am I wrong? Is it not that bad? The way to find out is to try it. Queries that generate large result sets are likely to write to disk anyway, even without an explicitly created table. Also, I tried the query you suggested ... can you really do this(?): insert into tmp select userId from iteneraries I was getting an error 'near insert into'. It seems you're trying to execute a subquery within a query ... can MySQL do this? It's not a subquery. The example I showed below consists of three separate queries. Make sure to terminate each with a semicolon. From the error message you describe, it sounds as though you may have issued them all as a single statement. Thanks. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 10:09 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 9:55 -0700 9/23/02, neal wrote: Thanks for the suggestion but this would actually create a new table, correct (the first statement that is)? I just want a resultset with these values, without writing to disk. Then you must upgrade to 4.x so that you have UNION support. Either that, or write a client program that issues multiple SELECT statements and buffers the results in memory. What's your objection to creating the new table? Just delete it when you're done with it. On another note, yeah youre right not a different connection object, but I presume I would need to run two seperate queries and recieve back two seperate resultsets. Neal -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 9:21 AM To: neal; mySQL Subject: RE: multi-table select (not a join) At 0:23 -0700 9/23/02, neal wrote: Oh man! Yeah, you're right. That's exactly what I want but apparently it wasn't implemented until v4??!?!?! Right. What did people do prior to version 4 when needing to query multiple tables? Just endure the overhead of multiple connections to the database? Not sure why you'd need multiple *connections*. You can use multiple *queries*, for example like this: CREATE TABLE tmp SELECT ... FROM t1 ... INSERT INTO tmp SELECT ... FROM t2 ... INSERT INTO tmp SELECT ... FROM t3 ... At the end of this, tmp will be the same as if you'd done UNION. More precisely, as if you'd done UNION ALL, because duplicates won't be removed. To remove them, use SELECT DISTINCT when retriving from tmp. Thanks. Neal - 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: multi-table select (not a join)
At 13:16 -0400 9/23/02, Jesse Sheidlower wrote: On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote: I had a question about the use of the UNION command in this context. The original poster asked about getting the name of the _table_ as well as some other data, which would seem to be relatively necessary for doing many types of things with the results of the query. For example, if you issue a query that gives you the union of seven different tables, and then you want to do another query based on these results, you'll need to know which of the seven tables a particular result came from. The docs on UNION don't seem to address this, none of the responses mentioned it, and I can't seem to find any discussion of how to retrieve the table name in a SELECT query (I acknowledge that most of the time you wouldn't need it, but in a UNION you might). How do you get the table name returned as part of the query results? Or am I misunderstanding how one would work with the results? Jesse Sheidlower You can't get the name of the table *from the query*. However, I assume that you already know the name of the table, or you wouldn't be able to write the query in the first place. :-) So just select an extra column: CREATE TABLE tmp SELECT 't1' AS tbl_name, t1.* FROM t1 ... ; INSERT INTO tmp SELECT 't2', t2.* FROM t2 ... ; INSERT INTO tmp SELECT 't3', t3.* FROM t3 ... ; When you're done, the first column will indicate the name of the table from which each row was obtained. - 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: multi-table select (not a join)
At 12:08 -0500 9/23/02, Paul DuBois wrote: At 9:55 -0700 9/23/02, neal wrote: Thanks for the suggestion but this would actually create a new table, correct (the first statement that is)? I just want a resultset with these values, without writing to disk. Then you must upgrade to 4.x so that you have UNION support. Either that, or write a client program that issues multiple SELECT statements and buffers the results in memory. I forgot another possibility. *IF* your tables all have identical structure and they are MyISAM tables, you can create a MERGE table from them and query the MERGE table. This will select from all the constituent MyISAM tables at once. identical = all columns and indexes the same - 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: multi-table select (not a join)
Jesse Sheidlower wrote: How do you get the table name returned as part of the query results? Or am I misunderstanding how one would work with the results? Try: SELECT *,'TABLEA' FROM TABLEA UNION SELECT *,'OTHERTABLE' FROM OTHERTABLE Herman - 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
