I'm stuck on this mySQL problem which uses the authors table: Display all Search ID and names of the authors. Below is what the output is supposed to look like. The Search ID needs the first 3 letters of the author's last name capitalized and the last 4 digits of their phone number. The output also needs the last name and first name of the authors.

This is what the author's table looks like:

I know the concatenate function needs to be used to combine them. I thought another way was to use the pipe symbol (||) but mySQL doesn't support it.

The closest I've come is this for the Search ID:

SELECT CONCAT(LEFT (au_lname, 3), UPPER(au_lname), RIGHT (phone, 4)) AS 'Search ID' FROM authors;

And this for the names:

SELECT CONCAT(au_lname, ', ', au_fname) AS "name" FROM authors ORDER BY au_lname;

I don't know how to combine these to get the output in the first image. Can someone help?

3 Display all Search ID and names of the authors. Search ID name BUC7223 Buchman, Sarah HEY7020 Heydemark, Wendy HUL4278 Hull, Hallie HUL4278 Hull, Klee KEL4680 Kells, Christian KEL7128 Kellsey, O'F0752 O'Furniture, Paddy | au id | au fname I au lname | phone I address | city | state | zip +-------+-------+ | A01 I Sarah | Buchman | 718-496-7223 | 75 West 205 St | Bronx | NY | 10468 | | A02 | Wendy | Heydemark | 303-986-7020 | 2922 Baseline Rd | Boulder | CO | 803031 | A03 | Hallie | Hull | 415-549-4278 | 3800 Waldo Ave, #14F | San Francisco | CA 1941231 | A04 | Klee | Hull | 415-549-4278 | 3800 Waldo Ave, #14F | San Francisco | CA 1941231 | A05 | Christian | Kells | 212-771-4680 | 114 Horatio St | New York | NY | 10014 | | A06 | Kellsey 1650-836-7128 | 390 Serra Mall | Palo Alto | CA | 94305 | | A07 | Paddy | O'Furniture | 941-925-0752 | 1442 Main St | Sarasota | FL | 34236 +------ ------+ 7 rows in set (0.13 sec) mysql> 1