Search code examples
mysqlphrase

MySQL how do I omit a selection in my output?

I currently am stuck on this question:

Display badge details for those employees who are not sales representatives. The format of the badge details is the first letter of the first name followed by a period, then the last name and then the title in upper case and enclosed in parentheses e.g. A. Fuller (VICE PRESIDENT, SALES)

Here is what I have so far: 

SELECT FirstName, LastName, 

CONCAT(SUBSTRING(FirstName, 1, 1),

'.',

(LastName),

' ',

UPPER(title)

) as 'Badge Details'

 from Employees;

Everything is working fine for me so far, I am just having a bit of trouble with the part of 'Display badge details for those employees who are not sales representatives', how do I go about NOT displaying Sales Reps? I assume I build a 'WHERE' at the end of my stuff, I just can't figure out how to word it.

Sample Data of the Badge Details output:

N.Davolio SALES REPRESENTATIVE
A.Fuller VICE PRESIDENT, SALES
J.Leverling SALES REPRESENTATIVE
M.Peacock SALES REPRESENTATIVE
S.Buchanan SALES MANAGER
M.Suyama SALES REPRESENTATIVE
R.King SALES REPRESENTATIVE
L.Callahan INSIDE SALES COORDINATOR
A.Dodsworth SALES REPRESENTATIVE

Thank you in advance :)

Solution

  • Assuming you can identify sales reps as having the title SALES REPRESENTATIVE, and also assuming that each employee can have only one role, then you should be able to just add a WHERE clause which removes the sales reps:

    SELECT FirstName,
       LastName,
       CONCAT(SUBSTRING(FirstName, 1, 1),
              '.',
              LastName,
              ' ',
              '(', UPPER(title), ')'

             ) AS `Badge Details`
FROM Employees
WHERE UPPER(title) <> 'SALES REPRESENTATIVE'