1

I have a table of contacts (primary key, firstname, lastname, prefix, suffix etc.). I am creating a contacts view to join this table with data from an organisation table (company name) In the view, I want the full name of the contact to appear so I am concatenating the firstname and lastname into fullname. If the first name or last name is null in the contacts table then this results in fullname being null in the contacts view.

I am creating the view using the following MySQL statement

CREATE VIEW view_contacts
AS
SELECT
contacts.PRIMARYKEY AS CONTACTPK,
CONCAT(contacts.FIRSTNAME, ' ', contacts.LASTNAME) AS FULLNAME,
contacts.EMAIL,
company.NAME AS ORGANISATION
FROM contacts
LEFT JOIN company ON contacts.COMPANYFK = company.PRIMARYKEY
ORDER BY contacts.LASTNAME ASC
Mureinik
  • 297,002
  • 52
  • 306
  • 350
JCorden
  • 11
  • 1
  • 1
    Yes - found further details here https://stackoverflow.com/questions/15741314/mysql-concat-returns-null-if-any-field-contain-null – JCorden Jul 08 '22 at 14:07

1 Answers1

1

You can use concat_ws to skip nulls:

CONCAT_WS(' ', contacts.FIRSTNAME, contacts.LASTNAME) AS FULLNAME,
Mureinik
  • 297,002
  • 52
  • 306
  • 350