Consider this first (based on your comment to @BK435 that you deleted):
create table t1
( id int auto_increment primary key,
stuff1 varchar(100) not null,
stuff2 varchar(100) not null
);
insert t1 (stuff1,stuff2) values ('111.1','111.2'),('111.3','111.4'),('111.5','111.6');
create table t2
( id int auto_increment primary key,
stuff1 varchar(100) not null,
stuff2 varchar(100) not null
);
insert t2 (stuff1,stuff2) values ('222.1','222.2'),('222.3','222.4'),('222.5','222.6');
select *
from t1
join t2
on t2.id=t1.id;
+----+--------+--------+----+--------+--------+
| id | stuff1 | stuff2 | id | stuff1 | stuff2 |
+----+--------+--------+----+--------+--------+
| 1 | 111.1 | 111.2 | 1 | 222.1 | 222.2 |
| 2 | 111.3 | 111.4 | 2 | 222.3 | 222.4 |
| 3 | 111.5 | 111.6 | 3 | 222.5 | 222.6 |
+----+--------+--------+----+--------+--------+
What happens with select * is that it grabs it all from the join on the two tables. It doesn't matter if it is real tables or derived tables with an Alias.
I also had the same column names in both tables to show the problem of not doing column aliases. The Below would fix that:
select t1.id,
t1.stuff1 as 1stuff1,t1.stuff2 as 1stuff2,t2.stuff1 as 2stuff1,t2.stuff2 as 2stuff2
from t1
join t2
on t2.id=t1.id;
+----+---------+---------+---------+---------+
| id | 1stuff1 | 1stuff2 | 2stuff1 | 2stuff2 |
+----+---------+---------+---------+---------+
| 1 | 111.1 | 111.2 | 222.1 | 222.2 |
| 2 | 111.3 | 111.4 | 222.3 | 222.4 |
| 3 | 111.5 | 111.6 | 222.5 | 222.6 |
+----+---------+---------+---------+---------+
Now back to what BK435 did. It gives the first derived table an Alias, so you get by that error, and it enlightens us with the error that TagsSignifikanzQuery comes out of left field, unknown, and causes an error there.
So let's say BK runs this first with blank tables resulting:
create table problemtag
( problemid int not null,
tagtext varchar(100) not null
);
create table problem
( problemid int not null,
Einzelsignifikanz int not null,
titel varchar(100) not null
);
Then runs his query. This is the error:
Error Code: 1054. Unknown column 'TagsSignifikanzQuery.ProblemID' in
'on clause'
So, when you join a,b,c,d etc (either real or derived table), it banks on the on clause to bring in the one you are working into the fold as having some relation to a (real or derived) table that is already in the join fold, if you will. But TagsSignifikanzQuery isn't. It is like someone threw Greek into the end of an English sentence. And the db engine is like 'huh?'
Edit (to illustrate your last error in Pink/Peach above)
create table t1
( id int auto_increment primary key,
stuff1 varchar(50) not null
);
insert t1 (stuff1) values ('111.1'),('111.2'),('111.3');
create table t2
( id int auto_increment primary key,
stuff2 varchar(50) not null
);
insert t2 (stuff2) values ('222.1'),('222.2'),('222.3');
create table t3
( id int auto_increment primary key,
stuff3 varchar(50) not null
);
insert t3 (stuff3) values ('333.1'),('333.2'),('333.3');
A1: an explicit Join
select t1.id,t1.stuff1,t2.stuff2
from t1
join t2
on t2.id=t1.id;
+----+--------+--------+
| id | stuff1 | stuff2 |
+----+--------+--------+
| 1 | 111.1 | 222.1 |
| 2 | 111.2 | 222.2 |
| 3 | 111.3 | 222.3 |
+----+--------+--------+
A2: an error just like yours (Error: 1054)
select t1.id,t1.stuff1,t2.stuff2
from t1
join t2
on t3.id=t1.id;
+----+--------+--------+
| id | stuff1 | stuff2 |
+----+--------+--------+
| 1 | 111.1 | 222.1 |
| 2 | 111.2 | 222.2 |
| 3 | 111.3 | 222.3 |
+----+--------+--------+
ERROR 1054 (42S22): Unknown column 't3.id' in 'on clause'
Why is my and your error the same? Because in my case, the select/join has no clue what I am talking about with t3, as the only thing it has been introduced to is t1 and t2 and I have not even completed the on clause for t2 yet. And I am referencing t3 (which exists as a table).
You may very well have a TagsSignifikanzQuery table. But it is just like my t3 in the join context. That is, the db engine has not been fed it correctly. You need to finish off the on clause bringing in t2. Then, continue with your join on TagsSignifikanzQuery if you have to.
But it errors out as written.
Edit2:
In what you wrote in comments:
... in the query "SELECT * FROM Table1 JOIN Table2" there is no AS clause needed although I would say you select data from a joined table which was not there before, i. e. a derived table. But the MySQL error goes "a derived table always needs an alias" or so, but it doesn't seem to apply to joined tables. Admittedly, an alias is not necessary in a joined table, but the error seems to make a wrong statement. or are joined tables not considered derived tables? -- from P.M.
Drew response:
in what you wrote from the beginning up through the words "which was not there before", well from my angle of reading that in just its own context not related to any other piece of code or where that code has been modified in some other state not shown, then Table1 and Table2 do exist or the join would fail. Right?
In the last third of what you wrote, I would say this: A table is either real or derived. If it is derived it needs a name, as seen in http://pastie.org/10379926 in lines 32 and 34 for an Answer I wrote up for this question: http://tinyurl.com/nhsmmyx
In line 34 I had to plop a y. Or I could have said "as y" but I am lazy. I would argue that I should not have to even do the "y" at all, as my outer-moust select of all that is inside is just the finishing touches and just should lazily give me the columns of the whole inner and be done with it. But mysql gives me "Error Code 1248: Every derived table must have its own alias" if I don't.
The reason on line 27 that I did "t" (or I could have said "as t") is because I am lazy and will bank on it in line 33, too lazy there to do "... and thing1.theDate<=x.xDate".
I hope this helps.
