How should i edit WHERE clause in SQL query to get desired result?-CodePudding

Let's say i have two tables called "Videos" and "Reviews"

Videos:
int: video_id (PK)
String: video_name
.
.

Reviews:
int: review_id (PK)
int: video_id (FK)
String: review
.
.

In a scenario where i want to get all videos and their related reviews, i use the query:

"SELECT * FROM Videos v, Reviews r WHERE v.video_id = r.review_id"

But this query only returns the videos with a review, the videos without a review is not returned, but i also need them.

How should i edit the query to see videos without a review too?

Example result:

(video_id), (video_name), (review_id), (review)
1, "videoName", 1, "review1"
2, "videoName2", NULL, NULL

Thank you for your answers and comments. (sorry for the typo)

CodePudding user response：

Seems you have some typos there - Travels or Videos Nevertheless, here is the query based on your posted query:

SELECT * FROM Travels t left join Reviews r on t.travel_id = r.review_id

Just a left join will do the trick.

CodePudding user response：

You use a "Left Join":

select v.video_id, v.video_name, r.review_id, r.review
from Videos v 
left join Reviews r on v.video_id = r.video_id;

(Your sample code is using old style join and it should really be the new style changing LEFT to INNER in the sample code)