CODEWITHSUNDEEP
mysqljoinconcatenation
philipp
philipp

Reputation: 57

Concat / Inner Join MySQL

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

Upvotes: 1

Views: 1569

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 2

Related Questions