Query of a query 1

[Michael57]

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?

 

[LNBruno]

Look up "join" and/or "union" as a start.

< M!ke >
Acupuncture Development: a jab well done.

 

[AlexCuse]

I don't think the files will be much help to you, but here's a starting point:

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:

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

 

[Michael57]

It looks like your calling out a table in this example. How do you call out the results of a previous query?

 

[AlexCuse]

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

 

[Michael57]

Im getting an invalid object name from this statement:
Select "Index"
from WorkRel_JobAdd.sql

WorkRel_JobAdd.sql is the name of the query

 

[AlexCuse]

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

 

[Michael57]

Sorry I'm pretty new at this. What is the code to generate the derived table?

 

[AlexCuse]

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

 

[Michael57]

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.

, 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.

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

 

[LNBruno]

How about starting here:

http://www.tek-tips.com/faqs.cfm?fid=3324

< M!ke >
Acupuncture Development: a jab well done.

 

[AlexCuse]

Hey Michael. Consider this:

[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

 

[chris123321]

Whats the difference between using a derived table and a temporary table? temporary table eats memory and slows down performance?

 

[AlexCuse]

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

 

[monksnake]

I'm scared of temp tables, I try to avoid them.


<.

 

[gmmastros]

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

 

[monksnake]

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.

<.

 

[chris123321]

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.

 

[AlexCuse]

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

 

[Michael57]

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.

, 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.

AS Expr2
FROM JobAddChgDetail INNER JOIN
JobAddChgHeader ON JobAddChgDetail.Ref = JobAddChgHeader.[Index] RIGHT OUTER JOIN
WorkRelease ON JobAddChgHeader.[Work Rel#] = WorkRelease.[Index]