Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: Query of query

  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: 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.

    Domy

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Code:
    SELECT
       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
    Johan

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    To make the answer more straight:

    Your query Q2 would be like

    SELECT * FROM (<YourSelectStatement>) Q1
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd say it would, and I'd even go for a function, just in case

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.

  9. #9
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    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.
    Johan

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "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
    Last edited by blindman; 09-03-03 at 14:09.

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    ROTFLMFAO!!

    blindman, you da man!
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm, have some bananas, you're coming up with severe case of verbal diarrhea

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ouch!

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by Paul Young
    ROTFLMFAO!!

    blindman, you da man!
    IDNUWYATA!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •