Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query of a query 1

Status
Not open for further replies.

Michael57

Technical User
Nov 15, 2005
131
CA
I have created to queries and from two diffent databases and saved them. SQL saves them in the project folder. How can I create a query that uses the results of the two queries?
 
Look up "join" and/or "union" as a start.

< M!ke >
Acupuncture Development: a jab well done.
 
I don't think the files will be much help to you, but here's a starting point:

Code:
select a.SomeCol, count(b.SomeOtherCol)
from
(
select * from PRODUCT
) a
inner join
(
select * from ORDERS
) b
on a.PRODUCT_ID = b.PRODUCT_ID

YOu can type out the queries and treat them as tables. Or, if you want to 'stack' the results, you could do this:

Code:
select a, b
from myTable
UNION ALL
select a, b
from myOtherTable

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
It looks like your calling out a table in this example. How do you call out the results of a previous query?
 
Ah, you beat me to the punch. Well played, LNBruno.

Michael57 - my point is, within those parentheses you can put any query you want, and SQL will treat it like a table (with the alias 'a' or 'b', depending on which pair of parentheses you put it in). This concept is called 'derived table'.

If you must store the results of a previously run query, then you will need to store them in a table. If it is acceptable to look at the CURRENT result of that query (not as it existed at some point in the past), then you can use a derived table to accomplish what you wish.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Im getting an invalid object name from this statement:
Select "Index"
from WorkRel_JobAdd.sql

WorkRel_JobAdd.sql is the name of the query
 
Dude, you can't select from the .sql file. You need to place the sql code (from within the .sql file) within the parentheses (so the query will be run, and its' result set treated for most intents and purposes like a physical table would be). If queries run in different db's you need to use full names (Database.Owner.TableName) for the tables.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Sorry I'm pretty new at this. What is the code to generate the derived table?
 
The code from your .sql file. I presume its' a select statement that you want to get the results from?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Ok so I have a query that has a select statement in it:

SELECT WorkRelease.[Index], WorkRelease.[Date Issued], WorkRelease.Job#, WorkRelease.Customer, WorkRelease.Part#, WorkRelease.[Part Name],
WorkRelease.[Kick Off Date], WorkRelease.
# said:
, WorkRelease.[Saturn Target Date], WorkRelease.[E/C Level], WorkRelease.Translation#,
WorkRelease.[Actual Due Date], WorkRelease.[Change Description], WorkRelease.[Work To Be Performed], WorkRelease.[Sample Requirements],
WorkRelease.[Initiated By], WorkRelease.Amount, JobAddChgHeader.[Start Date], JobAddChgHeader.[Due Date], JobAddChgHeader.PO#,
JobAddChgDetail.Die, JobAddChgDetail.Description, JobAddChgDetail.Amount AS Expr1, JobAddChgDetail.
# said:
AS Expr2
FROM JobAddChgDetail INNER JOIN
JobAddChgHeader ON JobAddChgDetail.Ref = JobAddChgHeader.[Index] RIGHT OUTER JOIN
WorkRelease ON JobAddChgHeader.[Work Rel#] = WorkRelease.[Index]

but is there something else I need in there so it writes the results to a derived table so I can requery these results? IM CONFUSED
 
Hey Michael. Consider this:

Code:
[COLOR=blue]select[/color] x.* [COLOR=green]--'outer query'
[/color][COLOR=blue]from[/color]
([COLOR=green]--'derived table' or 'subquery' starts here
[/color][COLOR=blue]SELECT[/color]      WorkRelease.[[COLOR=blue]Index[/color]], WorkRelease.[[COLOR=blue]Date[/color] Issued], WorkRelease.Job#, WorkRelease.Customer, WorkRelease.Part#, WorkRelease.[Part [COLOR=blue]Name[/color]], 
                      WorkRelease.[Kick [COLOR=blue]Off[/color] [COLOR=blue]Date[/color]], WorkRelease.[quote=#], WorkRelease.[Saturn Target [COLOR=blue]Date[/color]], WorkRelease.[E/C [COLOR=#FF00FF]Level[/color]], WorkRelease.Translation#, 
                      WorkRelease.[Actual Due [COLOR=blue]Date[/color]], WorkRelease.[[COLOR=blue]Change[/color] Description], WorkRelease.[[COLOR=blue]Work[/color] [COLOR=blue]To[/color] Be Performed], WorkRelease.[Sample Requirements], 
                      WorkRelease.[Initiated [COLOR=blue]By[/color]], WorkRelease.Amount, JobAddChgHeader.[[COLOR=#FF00FF]Start[/color] [COLOR=blue]Date[/color]], JobAddChgHeader.[Due [COLOR=blue]Date[/color]], JobAddChgHeader.PO#, 
                      JobAddChgDetail.Die, JobAddChgDetail.Description, JobAddChgDetail.Amount [COLOR=blue]AS[/color] Expr1, JobAddChgDetail.[quote=#] [COLOR=blue]AS[/color] Expr2
[COLOR=blue]FROM[/color]         JobAddChgDetail [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
                      JobAddChgHeader [COLOR=blue]ON[/color] JobAddChgDetail.Ref = JobAddChgHeader.[[COLOR=blue]Index[/color]] [COLOR=#FF00FF]RIGHT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color]
                      WorkRelease [COLOR=blue]ON[/color] JobAddChgHeader.[[COLOR=blue]Work[/color] Rel#] = WorkRelease.[[COLOR=blue]Index[/color]]
)[COLOR=green]--end of derived table [/color]
x[COLOR=green]--alias given to derived table[/color]

So basically, you are saying:
select * from derivedTable

but, as you noticed we can't do that. so we need to type:
select * from (select * from myTable)

which is really the same thing.

Does this make a little more sense now?

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom
 
Whats the difference between using a derived table and a temporary table? temporary table eats memory and slows down performance?
 
Chris -

The temporary table is not always necessary. There are actually cases with very large resultsets where a temp table (on disk) is faster than a table variable or derived table (both live in memory). A lot of it depends on how much memory you have available (I believe it ends up needing to use disk anyway if resultset is larger than available RAM, and this leads to a situation where it would have been better to just create on disk in the first place).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Whats the difference between using a derived table and a temporary table?

Huge.

A derived table is like writing in-line sql within a query. A temp table is a table that is created in the TempDB (one of the 4 system databases) and is actually written to the hard disk. Because of this, performance on a temp table can be pretty bad.

Earlier, Michael57 said "so it writes the results to a derived table". This is a mis-statement. Data is not written to a derived table. Data is gathered and presented "as though" it were a table. Anything you can do with a real table can be done with a derived table (if you know how).

A derived table almost always has better performance. My suggestion to you is... forget about temp tables. Try to write your queries using derived tables. If that doesn't work for you, then use table variables. Table variables are not written to the TempDB and are completely contained within memory, making performance better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
One of the main reasons I've never liked temp tables is because Uncle Rico uses them a lot, so I know they can't be good.

[monkey][snake] <.
 
Oops, I asked the wrong comparision..whens the appropriate time to use table vars compared to derived tables or what are the ads/disads of table vars to derived tables?

sorry about that.
 
Chris -

The only time that I really use table variables is when I need to do some kind of row-by-row processing, or when I want to have results of a relatively slow-running query available to my next step. OR if you will be using the same result set multiple times (better to populate the table variable once than create the derived table 6 times).

Another advantage of table variables is you can give them primary keys.

The only disadvantage I have seen I mentioned above, and it has to do with memory limitations. But you would run into the same problem using a derived table.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I get incorrect syntax after select??? Whats wrong? (thanks for hanging in with me)

select test.[Index]
from
SELECT WorkRelease.[Index], WorkRelease.[Date Issued], WorkRelease.Job#, WorkRelease.Customer, WorkRelease.Part#, WorkRelease.[Part Name],
WorkRelease.[Kick Off Date], WorkRelease.
# said:
, WorkRelease.[Saturn Target Date], WorkRelease.[E/C Level], WorkRelease.Translation#,
WorkRelease.[Actual Due Date], WorkRelease.[Change Description], WorkRelease.[Work To Be Performed], WorkRelease.[Sample Requirements],
WorkRelease.[Initiated By], WorkRelease.Amount, JobAddChgHeader.[Start Date], JobAddChgHeader.[Due Date], JobAddChgHeader.PO#,
JobAddChgDetail.Die, JobAddChgDetail.Description, JobAddChgDetail.Amount AS Expr1, JobAddChgDetail.
# said:
AS Expr2
FROM JobAddChgDetail INNER JOIN
JobAddChgHeader ON JobAddChgDetail.Ref = JobAddChgHeader.[Index] RIGHT OUTER JOIN
WorkRelease ON JobAddChgHeader.[Work Rel#] = WorkRelease.[Index]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top