-2

I have multiple tables which I need to merge into one after performing some operations into each one of them. A first nesting was achieved thanks to a (working) "WITH" statement:

With
T1 as (Select col1, col2, col3,...
from *database*
where *condition*)

Select t2.col1, t2.col2, t2.col3, ...
From(
Select
d.col1, d.col2, d.col3,...
from *d*
where *conditions*
Group by d.col1, d.col2, d.col3,...) t2

Inner join T1
 on t1.z = t2.x
Where t2.col1 = *condition*
and *conditions*
Group by t2.col1, t2.col2, t2.col3, ...

The problem arises when I try to expand on this and add more layers to the nest. I have tried to do the following (changes to the previous code are marked in between "**"):

With
T1 as (Select col1, col2, col3,...
from *database*
where *condition*)**,**

**T2 as (**
Select t2.col1, t2.col2, t2.col3, ...
From(
Select
d.col1, d.col2, d.col3,...
from *d*
where *conditions*
Group by d.col1, d.col2, d.col3,...) t2

Inner join T1
 on t1.z = t2.x
Where t2.col1 = *condition*
and *conditions*
Group by t2.col1, t2.col2, t2.col3, ...
**)**

**Select t3.col1 as qw, t3.col2 as qe, t3.col3 as qr,...**
**FROM(**
**Select**
**c.col1,**
**c.col2,**
**c.col3, ...**
**from *c***
**where *conditions) t3***

**Inner join t1**
**on t1.col3 = t3.qr**
**where t3.qe = *condition***
**group by t3.qw, t3.qe, t3.qr,...**

In return, I get the following error: "t3.qr": invalid identifier"

Does anybody knows what the issue is and how can I fix it? I need to figure out how to nest multiple tables in some way, as, after these ones, more table will have to be added

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
A.C.
  • 13
  • 4
  • 1
    which database are you using? – nbk Apr 14 '22 at 14:58
  • you write all your CTE's before writing select columns from cte's, tables joins sub-queries etc. you only have one select statement (apart from any you might have in cte's or sub-queries which are in brackets) –  Apr 14 '22 at 14:59
  • @Kendle, I do not understand, could you please rephrase? – A.C. Apr 14 '22 at 15:28
  • 1
    what i mean is that you need to add which rdms for example SQL server Oracle, MySqL and so on , else nobody can helo – nbk Apr 14 '22 at 16:25

1 Answers1

0

When we write a query we have to write things in the right order.

  • firstly CTE `with cte_alias as (select ... from ...)
  • the SELECT: select column_1, column_2 with possible agregate or window functions: SUM, MAX, ROW_NUMBER() etc. There should only be one SELECT which is not in brackets
  • FROM tables (sub-queries) CTE's etc with JOIN if needed
  • WHERE conditions which must be boolean (either true or false)
  • GROUP BY
  • ORDER BY only the select is obligatory in mySQL

SELECT Hello AS "speech"; is a valid mySQL query.