如何连接两个表以形成SQL查询？

Question

Ok, I have two tables: points and videos. I only want a video to show if the logged in user hasn't already been awarded points for watching that particular video.

The points db structure: id, special, user and points.

Special is the unique id of the video.

The videos db structure: id, title, points, src, photo, token, special.

There are multiple pages on this site. Token tells the video which page that video is ssigned to. Special is a special id for only that video and correlates to the special in the points db

--

This is the SQL Query I basicially want to say: $db->query("SELECT * FROM videos WHERE token='$id' AND num_rows OF user='$user' AND special='$special' == 0 FROM TABLE points");

Now I know this is not the proper format to write SQL Queries, but this is what I want in human. How do I translate this request to SQL?

Answer 1

You just need to join videos, points tables using the special column.

select count(*) from videos a join points b on (a.special = b.special) where b.user = '$user' and a.token = '$id';

This count will be 0 if the user has not watched the video.