SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using id from main select inside subquery

    I am wondering if it is possible to use result of main select inside subquery. I am giving the below example. It is not very clear query, but the only thing you need to know and I am asking about is how to use table1.id inside subquery. I am getting error "Unknown column 'table1.id' in 'on clause'". Is it even possible to use data from "outer" select inside subquery?

    SELECT
    (SELECT table2.budget FROM project_objects
    INNER JOIN project_objects AS table2 ON table2.project_id=table1.id),
    table1.id
    FROM project_objects AS ticket
    INNER JOIN projects AS table1 ON table1.id=ticket.project_id
    GROUP BY table1.id

    Thanks a lot!
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I'm... not sure why you have the subquery in the first place? You've got a useless table (ticket) in there as well....

    Perhaps you need to tell us what you're trying to do. Cause in my head, I've got this.

    SELECT SUM(table2.budget),table1.id
    FROM projects AS table1
    INNER JOIN project_objects AS table2 ON table1.id=table2.project_id
    GROUP BY table1.id;

  3. #3
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My example is much more complicated. That is why I gave simplier example. My query have more than 100 lines. But in general yes something like that.

    In main outer query I select table projects and group results by project_id. For each project id I need some extra subqueries that calculate some values in relation to this project id. Again, in real case the query is far much more complicated. And the only thing that is not working is how to use project id that is selected by outer query (after grouping) inside inner join inside subquery. In example I gave it, I get error "Unknown column 'table1.id'. So why the data about project id is not accessible inside subquery in this example?
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    use your subquery as a FROM table instead of a SELECT field, and then INNER JOIN them.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ernest1a View Post
    Is it even possible to use data from "outer" select inside subquery?
    yes, it is -- this is called a correlated subquery

    however, if you want to use the subquery in the outer query's SELECT clause, you ~must~ ensure that it is also a scalar subquery and returns only one column in only one row (i.e. a scalar value)

    more than this i cannot comment on, because i can't see your real query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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