Is there any way your usage of the query could measure your results, as in
"paging" or selecting a range of values by date, getting the top 100
records, etc.

Depending on the size of the table(s), a UNION, JOIN, or any other type of
relationship can make for a large number of records, killing performance.

_____

From: Garrett Smith [mailto:garrett@...]
Sent: Friday, December 17, 2004 12:13 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Temporary tables


I actually want three columns after the combination (I'm experimenting
with unions now):

name | foo_name | bar_name

where 'name' is your 'foobar.name'. For foo records, foo_name contains a
corresponding value, otherwise NULL. Same pattern for bar_name. There
are no common IDs between tables.

Looks like I could create a view that's a huge union, covering all
permutations, and thus avoid a temporary table. But I'm a tad nervous
about a) the size of the union SQL and b) the number of columns in the
view.

-- Garrett

Storage Box wrote:

> Sounds like you need a UNION - or - are you trying to treat both name
> columns as a single column with a single unified name?
>
> before "temp" table:
>
> foo.name | bar.name
>
> after combination:
>
> foobar.name
>
> _____
>
> From: Garrett Smith [mailto:garrett@...]
> Sent: Friday, December 17, 2004 11:46 AM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Temporary tables
>
>
> Ah, sorry, correction...the problem with joins (and I think this is
> also a problem with the view solution) is that I want to select
> something like:
>
> select * from ... where name = 'Blah'
>
> Note there's no table qualifier. I.e. I want the thing I'm querying to
> have a name column that is a merging of foo.name and bar.name. Thus
> the temporary table.
>
>
>
> -- Garrett
>
> Garrett Smith wrote:
>> This could be the best approach -- I was just trying to keep the SQL
>> simple. But I'm probably past that point :)
>>
>> -- Garrett
>>
>> Adomas Urbanavicius wrote:
>>> Why not
>>> View or query
>>>
>>> select * from foo
>>> left join bar on
>>> foo.id = bar.id
>>> where foo.name = 'Baz'
>>> or foo.status = 1
>>> /*
>>> or any other :
>>> bar.status = 1
>>> bar.name = 'Blah'
>>> */
>>>
>>>
>>>
>>>>
>>>> I have several tables that I want to query as if their rows were in
>>>> a single table. My thinking was to use a temporary table and
>>>> execute multiple 'select into' queries to populate it and then run
>>>> a final select against it.
>>>>
>>>> E.g., given two tables:
>>>>
>>>> foo ( id integer, name varchar)
>>>> bar ( id integer, name varchar, status integer)
>>>>
>>>> I want a structure looks like:
>>>>
>>>> some_structure (
>>>> name varchar,
>>>> status integer,
>>>> foo_name varchar,
>>>> bar_name varchar,
>>>> bar_status integer)
>>>>
>>>> so I can run queries like:
>>>>
>>>> select id from some_structure where name = 'Baz' or status = 1;
>>>> select id from some_structure where foo_name = 'Baz' or
>>>> bar_status = 1;
>>>>
>>>> Rows from table foo would NULL values for status. The underlying
>>>> applications lets users select objects (represented by id) by
>>>> specifying property name values. We validate input to ensure that
>>>> an unqualified properties (e.g. 'name', as opposed to 'bar_name')
>>>> have compatible types.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> ------------------------ Yahoo! Groups Sponsor
>>> --------------------~--> $4.98 domain names from Yahoo!. Register
>>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>>
>>
>

--------------------------------------------------------------------~->

>>>
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>
>>
>>
>> ------------------------ Yahoo! Groups Sponsor
>> --------------------~--> $4.98 domain names from Yahoo!. Register
>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>
>

--------------------------------------------------------------------~->

>>
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>

<http://us.ard.yahoo.com/SIG=129j18rva/M=298184.5639630.6699735.3001176/
D=gr

>

oups/S=1705115386:HM/EXP=1103395007/A=2495208/R=0/SIG=11egg01lg/*http://
www.

> netflix.com/Default?mqso=60188914> click here
>
>

<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=group
s/S=

>> HM/A=2495208/rand=815381014>
>
> _____
>
> Yahoo! Groups Links
>
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

>
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of

Service

> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>

--------------------------------------------------------------------~->

>
>
> Yahoo! Groups Links
>
>
>

Yahoo! Groups Sponsor

ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129p5ej0e/M=298184.5639630.6699735.3001176/D=gr
oups/S=1705115386:HM/EXP=1103396625/A=2495202/R=0/SIG=11evjk50a/*http://www.
netflix.com/Default?mqso=60188913> click here

<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=
:HM/A=2495202/rand=469013377>

_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]