Query of query

Hi,

just a question: I created a simple query (it contains a UNION) which returns tree colomuns. Since, I have to use the results of this query on another query and I prefer to avoid to use dirty ways like scripting, I was wondering it is possible to perform a select "on the fly" on the results of previous select: a kinf of "select from select"

To give an example I have this query which returns:
Q1: colA,colB,colC

Soon I run Q1, I would like to run over Q2 which is:
select * from Q1

Note: Q1 is not a table and even a view: it's just a select

Thanks if you would like to give me a trick.

DomySELECT
t.col1,
t.col2,
s.col2,
s.col3
FROM
table t inner join
(SELECT
col1,
col2,
col3
FROM
another_table) s on t.col1 = s.col1|||To make the answer more straight:

Your query Q2 would be like

SELECT * FROM (<YourSelectStatement>) Q1|||jora's is the most efficient method of solving this in terms of code. If you actually need the results of Q1 twice ("Soon I run Q1, I would like to run over Q2") and the query is processor intensive, then run it once and store the results in a temporary table or table variable. Then you can quickly join it in further statements as often as you want.

blindman|||jora's code answers some other question that has not been asked, I believe, while DB's answer is exactly what the original posting was after.

I don't get bm's comments, - are you the judge of the forum? Or your word should always be last, even if it's wrong? Any point to that?

And speaking about efficiency, DB's suggestion is actually more efficient, hands down.|||If you use the results from the first query regularly, would it not make more sense to create a view and then query the view?|||I'd say it would, and I'd even go for a function, just in case :)|||You would just want to make sure that you add an index on the table for the rows you're selecting against in your view, and then you can index your view.|||it's normally not my thing to criticize other peoples posts, but while we're at it here it goes. Sure, my join was not asked, but i didn't read about views, temporary tables and functions as well. Moreover DomyFerraro asked for a select statement on the fly.|||"SELECT * FROM (<YourSelectStatement> ) Q1"???

What the heck does that contribute?! Hey, I can make it even more efficient, djabarov: how about just <YourSelectStatement>?

Or if (as I suspect) DB gets paid by the amount of code he writes, this would be more lucrative:
"SELECT * FROM (SELECT * FROM (SELECT * FROM (<YourSelectStatement> ) Q1) Q2) Q3"

DB's solution can't be more efficient than Jora's, because it is exactly the same as Jora's, just less informative.

Jora's is the best solution for on-the-fly processing. Mine is more efficient if the dataset has to be used multiple times. A view may be usefull if the join is used in multiple procedures, though it might be less efficient because it would not be pre-compiled.

djabarov, do you think about your posts, or does your head just fall onto the keyboard while you are napping?

blindman|||ROTFLMFAO!!

blindman, you da man!|||bm, have some bananas, you're coming up with severe case of verbal diarrhea :D|||Ouch!|||Blindman, did you understand DomyFerraro's problem? See his original question, and read loud and clear:

He has a query Q1, which is a union query. he wants to use this query on-the-fly, as it would be SELECT * FROM Q1. He does not want your view, or a (temporary) table.

I gave him just this solution. So, open up your eyes!|||Originally posted by Paul Young
ROTFLMFAO!!

blindman, you da man!

IDNUWYATA!|||Originally posted by DoktorBlue
He does not want your view, or a (temporary) table.


I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.|||Originally posted by DoktorBlue
IDNUWYATA!

OISTUYJF!|||Originally posted by Paul Young
OISTUYJF!

Great, this guru stuff!|||Originally posted by Seppuku
I was the one who made the comment regarding the view. I simply meant it as an alternative, but not necessarily a "solution", and something to research if curious.

Apperently only the "correct" answer may be posted and differing views or opinions are not tollerated.|||That appears to be the rule that you and bm are imposing here.|||You crack me up, DoktorBlue!
Your "solution" is meaningless, and wouldn't solve any problem EVER posted on this forum. I'll try to make this simple for you:

There is NO syntactical difference between your solution...
SELECT * FROM (select from A union select from B) Q1
...and a straight union query...
select from A union select from B
NOTHING is gained by wrapping the union statement in a subquery.

If you can't see this, and can't figure out that DomyFerrao gains NOTHING from it, then there really is no hope for you. Go take a course in hardware maintenance or something. DUH.

As far as me not understanding the problem, didn't you make yourself look foolish enough on mohan1976's posted question (http://dbforums.com/t901820.html) when you told me my posting "must be mistaken from another thread", that I was "exposing yourself incapable understanding the problem", and to "Contribute to the thread, or be quiet!" AND THEN MOHAN USED MY SOLUTION INSTEAD OF YOURS!

It is not my goal to make you look foolish. That's not why I post here, I have better things to do with my time; but I will respond to your more ridiculous comments. You seem to think I'm gunning for you, but in reality it is you who keeps shooting yourself in the foot by attacking every comment I post.

Talking to you is like trying to teach a chimp how to tie shoelaces when you know it is never going to put on a pair of sneakers anyway.

blindman|||Cool off, bm, no need for Zoo analogies here, it's just a forum, not a battle field :)

I think that if you look at the original question which you quoted yourself, - you might see that an attemp to SELECT * FROM (select * from A union select * from B) Q1 may not necessarily be so straight forward. I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? Try to leave some room for the possibility that DomyFerraro simply left out a presence of WHERE clause that may require the inner query to be left within the parenthesis rather than be join with the original table as jora suggested.

Other than that, why can't we all be more constructive while trying to help each other, rather than master our wit in insulting our "opponents"?

Can it be done? At all? Without recommending to increase the dosage of certain medication or referencing WWW.DICTIONARY.COM??

People are here not to see us being ugly, but to seek help. And if one happens to know more than the other, - at the end everybody wins.

Can we all try, just once?

Thanks in advance :)|||"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Hey! We agree! Friends now?|||I am all for it! Don't exclude DoktorBlue! He is a good guy, I happened to get to know him a little more via private email, - he has A LOT to offer, I mean it, much more than I can dream of.|||Originally posted by rdjabarov
That appears to be the rule that you and bm are imposing here.

If you had taken the time to look over my prior posts, rather than the past few threads, you would realize how utterly stupid your comment was.

I am more than happy to drop all this, but don't expect me to sit back and take crap off people with out a response. I did not start this pissing contest.

Most problems posted here have many potential solutions. The ironic thing is that in all this debate DomyFerraro has not posted back asking for more help or suggested which solution best fit his problem.|||Originally posted by blindman
"I don't believe that DomyFerraro just wanted to select from a sub-query for the heck of it. Don't you think? "

I absolutely agree, and that is what Jora's solution explained; how to embed the results of the UNION query in a more complex statement.

Blindman: I'm glad that you finally got the point, thanks to rdjabarov. I assumed everybody, including you, te be familiar with the fact, that a SELECT statement may be more complex than just SELECT * FROM x. To make the principle more clear to DomyFerraro (where are you?), I gave a straight example without any extras. You called it with many words meaningless, however, it does show in its basic form how to select from a query. Regarding to Mohan's thread: yes, he is using your solution, which isn't giving him, what he has specified. As long as he's glad, I'm glad, too.

Paul: I guess, you already found out by yourself, that this isn't about the one-and-only path to Rome, but about blindman's almost structural misunderstandings.|||Hello buddies,

first let me thank you for so big support you gave me. I had the change to verify all the suggestions which I like and here the solution which fits better for my needs (already tested):

select * from
(
select * from T1
UNION
select * from T2
)

Believe, behind the two nested and root queries there is something more complexed, but the idea it's enough for me since, I have no problem about performance.

Open to any comments.

Thanks|||Sorry, I forgot to put the alias so:

select * from
(
select * from T1
UNION
select * from T2
) Q1

and here the draft of real query (just fyi):

Select * from
(
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_finito)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds1,
0 ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='999'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
UNION
(
select
[Fatture Prodotti uscita].IDProdottiUscita,
0 as ds1,
cast (((cast((datediff(d, [Fatture Dati].Data, [Fatture Dati].Data_grezzo)) as decimal )) / (datediff(d, [Fatture Dati].Data, [Turni di lavorazione operazione].Data_turno))*100) as int) as ds2
from
[Fatture Dati],
[Fatture Prodotti uscita],
[Elenco Disegni],
[Operazioni per disegno],
[Turni di lavorazione operazione]
where
[Fatture Dati].IDFattura = [Fatture Prodotti uscita].IDFattura
and [Fatture Prodotti uscita].IDProdottiUscita = [Elenco Disegni].IDProdottiUscita
and [Elenco Disegni].IDDisegno = [Operazioni per disegno].IDDisegno
and [Operazioni per disegno].IDOperazione = [Turni di lavorazione operazione].IDOperazione
and [Operazioni per disegno].cc_pln ='129'
and (left([Fatture Prodotti uscita].IDProdottiUscita,1) = '5')
and datediff (d, '01/01/2003' , [Fatture Dati].data ) > 0
)
) Q1|||Originally posted by DomyFerraro
select * from
(
select * from T1
UNION
select * from T2
) Q1


Your table and field names sound like italo music ...:)

Blindman's point was, that you don't need the outer SELECT frame in it's basic form, if you don't use any other SELECT options like a specific SELECT clause, GROUP BYs, ORDER BYs, or using your query Q1 as an entity of joining.|||I see Blindman's point, we should also consider I need to group the results of "nested" query, so the first solution, which is more pratical, for me is readble and easy to maintain.
At this time, because I have no performance problem all around, yours sounds good.

Thanks for sure to Blindman too.

ps: Yes: it's italian as well me