I’m using SQLite3 and DB Browser for manage my database for a web project, it’s a convention register manager. There I have this table
CREATE TABLE "Tickets" (
"code" TEXT,
"name" TEXT NOT NULL,
"category_id" INTEGER,
"status" TEXT NOT NULL,
FOREIGN KEY("category_id") REFERENCES "TicketCategories"("id"),
PRIMARY KEY("code")
);
Here I have a foreign key with the table TicketCategories, this other table:
CREATE TABLE "TicketCategories" (
"id" INTEGER,
"name" TEXT NOT NULL,
"price" DECIMAL(10, 2) NOT NULL,
"description" TEXT,
PRIMARY KEY("id" AUTOINCREMENT)
);
The point is, I want to made a view where the "category_id" it appear as the name of the ticket category in "TicketCategories" table, I made this view for do that:
CREATE VIEW TicketInfo AS
SELECT
Tickets.code,
Tickets.name AS ticket_name,
TicketCategories.name AS category_name,
Tickets.status
FROM
Tickets
JOIN
TicketCategories ON Tickets.id_cat = TicketCategories.id;
But when I run it, the view appears empty, without any data:
enter image description here
I’ve already made another similar view and it works, I dont know why this view don’t work. I tried do some changes and nothing.
I have tried to change the view creation code like this
CREATE VIEW TicketInfo AS
SELECT
t.code,
t.name AS ticket_name,
tc.name AS category_name,
t.status
FROM
Tickets t
JOIN
TicketCategories tc ON t.id_cat = tc.id;
and I tried to use LEFT JOIN
, but it didn’t solve anything
You need to sign in to view this answers