I have the following tables:
CREATE TABLE title (
booktitle VARCHAR( 60 ),
title_id CHAR( 6 ),
au_id CHAR( 11 ),
PRIMARY KEY (title_id)
)
create table authors (
au_id char(11),
au_lname varchar(20),
au_fname varchar(20),
phone varchar(10),
address varchar(30),
city varchar(20),
state char(2),
zip char(5));
I need to somehow show the booktitle, au_lname and au_fname in one field. I want to order it by CITY, but that should be simple enough. So far 开发者_高级运维I have made a view of this data, but I am not sure how to make it into a single field? I tried using the "AS" command, but I keep getting errors.
Here is my working view:
CREATE VIEW authorname AS
SELECT
title.booktitle, authors.au_fname, authors.au_lname
FROM
title
INNER JOIN authors ON title.au_id = authors.au_id
ORDER BY authors.city
You can use CONCAT()
to concatenate string values:
CREATE VIEW authorname AS
SELECT
CONCAT(title.booktitle, authors.au_fname, authors.au_lname) as name
FROM title
INNER JOIN authors ON title.au_id = authors.au_id
ORDER BY authors.city
or if you want to separate the values by a certain character, use CONCAT_WS()
:
CONCAT_WS(' ', title.booktitle, authors.au_fname, authors.au_lname) as name
精彩评论