Home > Software design >  Adding NOT NULL constraint to one SELECT Column
Adding NOT NULL constraint to one SELECT Column

Time:02-03

I am trying to creat a view of database.

I would need to select only the values from Comments.FK_CommentID which are NOT NULL but I can't seem to know how to solve it.

Here's my view:

CREATE VIEW Comments_of_comments AS SELECT User.Username AS User, Comments.content AS Comment, Comments.FK_CommentID AS 'Commented on' FROM Comments INNER JOIN User ON Comments.UserID = User.UserID ORDER BY User ASC;

Here's the output as of now:

User Comment Commented on
Anthony Hello 3001
Brian I'm glad
Charlie I'm sad 3000
Dylan All ok 3004
Ernie Let's go

But I would like it to be WHERE Comments.FK_CommentID IS NOT NULL. (Commented on -column)

User Comment Commented on
Anthony Hello 3001
Charlie I'm sad 3000
Dylan All ok 3004

CodePudding user response:

If you just want to view your data this way, then add a WHERE clause filtering off the null commented on records:

CREATE VIEW Comments_of_comments AS
SELECT u.Username AS User,
       c.content AS Comment,
       c.FK_CommentID AS "Commented on"
FROM Comments c
INNER JOIN User u ON c.UserID = u.UserID
WHERE c.FK_CommentID IS NOT NULL
ORDER BY User;
  • Related