MySQL query select/join concat error

Question

What is the correct way to perform this query? Additionally, I'd like to trim spaces from fullname, because if middle is empty it still returns the spaces before and after it.

SELECT first,middle,last, 
CONCAT(first,' ',middle,' ',last) AS fullname
FROM names a 
LEFT JOIN info b ON fullname = b.name
LIMIT 1

the current error is: ERROR 1054 (42S22): Unknown column 'fullname' in 'on clause'

Apart from the answers provided regarding the derived column join, you can use CONCAT(first, COALESCE(CONCAT(' ', middle, ' '), ' '), last) AS fullname for the fullname — Zane Bien
– Zane Bien, Commented Jul 13, 2012 at 4:31

Adriaan Stander · Accepted Answer · 2012-07-13 04:23:26Z

3

The problem that you are facing is that you are trying to join on a calculated column.

You need to first calculate the column in a subselect, and then join to that 'table'

Something like

SELECT  *
FROM    (
            SELECT  first,
                    middle,
                    last,  
                    CONCAT(first,' ',middle,' ',last) AS fullname 
            FROM    names 
        ) a  LEFT JOIN 
        info b  ON  fullname = b.name 
LIMIT 1

You can also have a look at the CASE Statement for the spaces in the middle.

edited Jul 13, 2012 at 4:23

answered Jul 13, 2012 at 4:16

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Poe Over a year ago

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(first,' ',middle,' ',last) AS fullname FROM names' at line 6

Poe Over a year ago

Thanks, that looks great and it worked. I modified the CONCAT() line using the IF() provided by sel below, to fix my 2nd issue.

ypercubeᵀᴹ Over a year ago

You can also do: ON CONCAT(n.first,' ',n.middle,' ',n.last) = b.name

sel · Accepted Answer · 2012-07-13 04:19:03Z

1

You can't use an alias in the ON clause. Try doing

SELECT first,middle,last,
CONCAT(first,' ',middle,' ',last) AS fullname
FROM names a 
LEFT JOIN info b ON b.name=CONCAT(first,' ',middle,' ',last)

"The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause" (from dev.mysql.com/doc/refman/5.1/en/join.html).

So as a logical inference you're not allowed to use aliases in ON clauses.

answered Jul 13, 2012 at 4:19

sel

4,9421 gold badge18 silver badges23 bronze badges

2 Comments

Poe Over a year ago

I was afraid I'd have to do something like that, but hoped for a more elegant answer. Any chance of tackling the consecutive spaces between first and last when a middle is empty?

sel Over a year ago

SELECT IF(middle='', CONCAT(first,' ',last), CONCAT(first,' ',middle,' ',last) ) as fullname

Sashi Kant · Accepted Answer · 2012-07-13 04:23:39Z

0

Try this:

select first, last, CONCAT(first,' ',middle,' ',last) as fullNAME from names a left join info b on (CONCAT(first,' ',middle,' ',last) =b.name)

one more thing, u have skipped a comma after last in ur select query

answered Jul 13, 2012 at 4:23

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Comments

9ine · Accepted Answer · 2012-07-13 04:25:45Z

0

try this ,

SELECT CONCAT(first,' ',middle,' ',last) AS fullname FROM names a

LEFT JOIN info b

ON fullname = b.name

LIMIT 1

answered Jul 13, 2012 at 4:25

9ine

8593 gold badges8 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL query select/join concat error

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related