How to replace the column value with value from other connected table

deeded307 0
Viewed 26

The code below is my query code of postgresql schema views.

Please assuming this a library table, which is a book list and you have some defined tags can apply on the book itself, and every book will be devided into one category.

CREATE VIEW tagging_books AS
SELECT tags."TagName", books."BookISBN", books."BookName", books."BookCategoryID"
FROM library
    INNER JOIN tags on library."TagName_id" = tags."id"
    INNER JOIN books on library."BookISBN_id" = books."id"
    ORDER BY tags."id"

The schema views inside db will looks like this:

/tags.TagName   /books.BookISBN      /books.BookName    /books.BookCategoryID
Python          ISBN 957-208-570-0   Learn Python       1

And the BookCategoryID from table "books" is actually a foreign key of table "category", the table looks like this:

/category
BookCategoryID   CategoryName
1                Toolbook

I wonder that, is there anyway to replace the books."BookCategoryID" field to category."CategoryName" by query code? Like the example below.

/tags.TagName   /books.BookISBN      /books.BookName    /category.CategoryName
Python          ISBN 957-208-570-0   Learn Python       Toolbook

Since they are connected with each other, I think they can definitely being replaced, but I don't know how to do... Thank you.

sql postgresql
1 Answers

To include category.name, simply join with table category on the foreign-key constraint like:

select category.name, books.*
from books
join category on books.BookCategoryID = category.BookCategoryID

You can add it to your view creation as well:

CREATE VIEW tagging_books AS
SELECT tags.TagName, books.BookISBN, books.BookName, category.name as "CategoryName"
FROM library
    JOIN tags on library.TagName_id = tags.id
    JOIN books on library.BookISBN_id = books.id
    JOIN category on books.BookCategoryID = category.BookCategoryID
ORDER BY tags.id
treuss0
Related