Concat / Inner Join MySQL

Question

im trying to create an sql query with a concat and inner join. Without the inner join everything works fine, using the inner join i get:

ERROR 1054 (42S22): Unknown column 'kk.kundennummer' in 'field list'

This is my query:

select 
    kk.kundennummer as customer_id,
    kk.nummer as customer_nr,
    substr(`name`, 1, (length(`name`) - length(
        SUBSTRING_INDEX((`name`), ' ', -1))-1)
        ) as first_name,
    substring_index(`name`,' ',-1) AS last_name,
    k.name1 as company_name,
    kk.Abteilung as department,
    concat(`kk.kundennummer`,`kk.Nummer`) as customer_key, 
    kk.Telefon as phone,
    kk.Telefax as fax,
    kk.Handy as mobile,
    kk.E_Mail as email,
    kk.Geburtstag as birthday,
    kk.Autotelefon as mobile2
from kundenkontakt kk 
    inner join kunden k on (k.Kundennummer = kk.Kundennummer) 
where kk.E_Mail != "";

What im doing wrong?

Regards

are you on a case sensitive System where a capital K is not the same like a lower case k? — Simulant
– Simulant, Commented Oct 11, 2015 at 12:09

Gordon Linoff · Accepted Answer · 2015-10-11 12:09:15Z

2

The problem is this line of code (and any similar ones I might have missed):

concat(`kk.kundennummer`,`kk.Nummer`) as customer_key,

You have no column called "kk.kundennummber" (with the "kk." as part of the column name). Just remove the backticks:

concat(kk.kundennummer, kk.Nummer) as customer_key,

Hmm, this is a good reason not to use backticks when writing code. I think they just clutter up queries (unless absolutely necessary), but they can also lead to unexpected errors.

answered Oct 11, 2015 at 12:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

philipp Over a year ago

Thank you! I searched a long.

Collectives™ on Stack Overflow

Concat / Inner Join MySQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related