Convert data in a table with another data when displaying

Question

I have a Status data inside a table that has 3 possible states (active, inactive, unknown). Similar to this one , I want to make the Status only accept numbers (1,2,3) and then convert to the 3 possible states when displaying the table. I've tried using FK for this and reference the Status data to the Status table but I don't know how to 'link' between the two so that when displaying the table, instead of displaying numbers, it displays the Status in words that corresponds to the number it stores. I also thought about using view and join but, again, I don't have any clue.

DROP TABLE IF EXISTS student_data;

DROP TABLE IF EXISTS Major;

DROP TABLE IF EXISTS Academic_Status;

CREATE TABLE Major(
    Id INT PRIMARY KEY,
    Major VARCHAR(35) NOT NULL UNIQUE
);

CREATE TABLE Academic_Status(
    Id SERIAL,
    Academic_Status VARCHAR(15) NOT NULL UNIQUE
);

INSERT INTO Academic_Status (Academic_Status) VALUES
('Inactive'), ('Active'), ('Unknown');

CREATE TABLE student_data (
    Id SERIAL,
    Name VARCHAR ( 50 ) NOT NULL,
    NPM CHAR ( 10 ) NOT NULL UNIQUE,
    Date_of_Birth DATE NOT NULL,
    Major VARCHAR ( 35 ) NOT NULL,
    Academic_Status_ID INT REFERENCES Academic_Status(Id)
);

INSERT INTO student_data (Name, NPM, Date_of_Birth, Major, Academic_Status_ID) VALUES
('Fadhilah Akbar Suherman', '1906355577', '2001-05-17', 'Computer Enginnering', 2),
('Evans Hebert', '1906355367', '2001-03-07', 'Electrical Enginnering', 2),
('Muhammad Hadi', '1906355189', '2001-05-21', 'Mechanical Enginnering', 3),
('Ronald Grant', '1906355235', '2001-01-12', 'Chemical Enginnering', 1),
('Theodorus Lucas', '1906354594', '2001-11-23', 'Computer Enginnering', 2),
('Yusuf Agung', '1906355342', '2001-08-25', 'Electrical Enginnering', 1);

SELECT * FROM student_data
    INNER JOIN Academic_Status ON student_data.Academic_Status_ID = Academic_Status.Id;

INSERT INTO student_data (Name, NPM, Date_of_Birth, Major, Academic_Status_ID) VALUES 
('Muhammad Rafly Yanuar', '2006468661', '2002-01-17', 'Computer Enginnering', 2);

@MitchWheat I forgot to mention that. I already looked that up. I think I have to use the INNER JOIN one. I tried it, couldn't make it work. — Rafly Yanuar, Feb 26 '22 at 14:27
"I tried it, couldn't make it work." - is not helpful. I'd suggest posting your attempt. — Mitch Wheat, Feb 26 '22 at 14:28
@MitchWheat Edited the post. The SELECT * FROM ... doesn't show anything. — Rafly Yanuar, Feb 26 '22 at 14:34
where do you assign the Academic_Status_ID values in table Academic_Status ??? — Mitch Wheat, Feb 26 '22 at 14:37
@MitchWheat I'm sorry but I can't quite understand what you mean. I edited the code again BTW. Change the PRIMARY KEY in table Academic_Status to SERIAL. — Rafly Yanuar, Feb 26 '22 at 14:45

Mateen · Answer 1 · 2022-02-26T16:30:03.880

0

You can create a SQL view to display your respected columns.

CREATE VIEW TEMP
As 
SELECT sd.Name, sd.NPM, sd.Date_of_Birth, s.Academic_Status 
FROM student_data sd
INNER JOIN Academic_Status s
ON sd.Academic_Status_ID = s.Id

After creating the view please execute this query for the result.

SELECT * FROM Temp

Please make sure that data types will be the same for Academic_Status_ID in student_data and Id in Academic_Status table.

edited Feb 26 '22 at 16:30

answered Feb 26 '22 at 14:54

Mateen

503
2
10

Thanks for your answer. I see that you've altered some names. Can you please specify what have you altered? This is my attempt `CREATE VIEW TEMP AS SELECT sd.Name, sd.NPM, sd.Date_of_Birth, sd.Major, sd.Academic_Status_ID FROM sd INNER JOIN Academic_Status ON sd.Academic_Status_ID = sd.Id;` It shows nothing, BTW. – Rafly Yanuar Feb 26 '22 at 15:05
I have updated my answer please execute the second query to display the result, also your query in the comment is not correct and not the same as mine, especially the ON condition. – Mateen Feb 26 '22 at 16:31
Your code works but not the way I want. I want the Academic_Status_ID column replaced by Academic_Status in table Academic_Status when displaying, not both of them. I already found the solution. Thanks! – Rafly Yanuar Feb 27 '22 at 02:38

Convert data in a table with another data when displaying

1 Answers1