List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 23 2002 5:17pm
Subject:RE: multi-table select (not a join)
View as plain text  
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:paul@stripped]
>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:paul@stripped]
>>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

Thread
multi-table select (not a join)neal23 Sep
RE: multi-table select (not a join)neal23 Sep
  • RE: multi-table select (not a join)Paul DuBois23 Sep
    • RE: multi-table select (not a join)neal23 Sep
      • RE: multi-table select (not a join)Paul DuBois23 Sep
        • RE: multi-table select (not a join)neal23 Sep
          • RE: multi-table select (not a join)Paul DuBois23 Sep
        • RE: multi-table select (not a join)Paul DuBois23 Sep
    • Re: multi-table select (not a join)Jesse Sheidlower23 Sep
      • Re: multi-table select (not a join)Paul DuBois23 Sep
RE: multi-table select (not a join)Herman Verkade23 Sep