1

Since I got the same error type explained in some many other stackoverflow questions:

MySQLSyntaxErrorException: Every derived table must have its own alias

After carefully reading it including every single duplicate question (like this one for example), I have tried to assign for every derived table a specific alias.

This is my example query:

SELECT pl.*, p.cat_1, pb.id 
FROM table_1 AS pl
JOIN table_2 AS p ON p.id=pl.column_id 
JOIN table_3 AS pb ON pb.id=p.id
JOIN table_4 AS u ON u.id=pl.user_id
WHERE pl.value_1=2 AND pl.value_2 > 0 
ORDER BY id desc

Probably I'm doing something wrong, maybe this is not the best way to write the query? I don't know but without any suggestions I can not understand which is the problem, also because the query in concrete works well if it's executed.

Any suggestions?

Community
  • 1
  • 1
UgoL
  • 839
  • 2
  • 13
  • 37
  • Remove AS in the alias also use alias in order by clause – Jim Macaulay May 18 '17 at 11:22
  • ORDER BY id desc use alias table name for id example like ORDER BY pb.id desc – JYoThI May 18 '17 at 11:24
  • 1
    Your query has no derived table. Hence, that query would not generate that error. – Gordon Linoff May 18 '17 at 11:27
  • This sounds really strange. Maybe the problem is that I'm trying to execute this query via Knime mysql node? – UgoL May 18 '17 at 11:36
  • @UgoL . . . That could be causing a problem. I would suggest starting by running the query directly against the database. – Gordon Linoff May 18 '17 at 11:41
  • Hi @Gordon, finally I have realized that the problem does not exist. The query executed using MySQL WorkBench works like a charm without the alias console message. Probably the Mysql node in Knime is affected by this warning since it works differently, it uses `jdbc.exceptions` in java. I think we can close this question, do you know how can I do it? Thanks. – UgoL May 18 '17 at 12:09
  • @UgoL . . . As the author of the question, you should be able to delete the question. – Gordon Linoff May 18 '17 at 12:11

1 Answers1

0


Try using below query,

SELECT pl.*, p.cat_1, pb.id 
FROM table_1  pl
JOIN table_2  p ON p.id=pl.column_id 
JOIN table_3  pb ON pb.id=p.id
JOIN table_4  u ON u.id=pl.user_id
WHERE pl.value_1=2 AND pl.value_2 > 0 
ORDER BY pb.id desc
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • Thank you @Jim, unfortunately this is not the solution. I have already tried to execute again the query without the AS for each alias, but the problem still remains. – UgoL May 18 '17 at 11:35