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
