Using id from main select inside subquery

Databases
1

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=[B]table1.id[/B]),
        table1.id
      FROM project_objects AS ticket 
      INNER JOIN projects AS table1 ON [B]table1.id[/B]=ticket.project_id 
      GROUP BY table1.id

Thanks a lot!

2

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

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?

4

use your subquery as a FROM table instead of a SELECT field, and then INNER JOIN them.

5

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