0

I'm trying to set a variable for x.* and y.* like this:

("SELECT x.* as x_var, y.* as y_var 
  FROM table_x x 
  INNER JOIN table_y y on x.id = y.id WHERE x.id='1'");

But the code gives an error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as table_x, y.* as y_var FROM table_x x INNER JOIN table_y d ON x.id = y.id WH' at line 1

How can I fix it?

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
Alden Willms
  • 27
  • 5
  • 7
    You need to set aliases one-by-one; `* as` doesn't make sense. – Gordon Linoff Jan 11 '19 at 15:03
  • 1
    like u.id as uid, y.something as y_smt etc? – Alden Willms Jan 11 '19 at 15:04
  • The error is expected behavior given the malformed SQL syntax. I strongly recommend that we ditch the `*` placeholder in the SELECT list, and explicitly list the columns to be returned. Each individual expression (column) in the SELECT list can be assigned an alias. `SELECT x.var AS exvar, x.foo AS exdotfoo, y.bar as wydottbar ...` – spencer7593 Jan 11 '19 at 15:12
  • Thank you, so there's no other ways to name the x.* right? The only way is to is giving names for each column such like >x.foo as xbar – Alden Willms Jan 11 '19 at 15:22

1 Answers1

0

With respect, your use of "variable" is ambiguous. Please read this: MySQL: @variable vs. variable. What's the difference?

If you mean column aliases, you do

 SELECT col1 a, col2 b, col3 c
   FROM whatever

You tried to assign multiple aliases with SELECT * AS alias. You Can't Do That™.

If you mean local variables you want

 SELECT @a := col1, @b := col1

And, you can't do SELECT @a: = * either.

Pro tip: Avoid using SELECT * whenever you can. Instead give a list of column names you want.

O. Jones
  • 103,626
  • 17
  • 118
  • 172