如何从总记录中获取不存在的记录。

Table1: tbl_users:

表1:tbl_users:

+----+--------+
| id | name   |
+----+--------+
| 1  | waheed |
+----+--------+
| 2  | fareed |
+----+--------+

Table2: tbl_watched:

表二:tbl_watched:

+------------+----+--------+
| id_watched | id | name   |
+------------+----+--------+
| 1          | 2  | fareed |
+------------+----+--------+

i want to get the total records if the tbl_watched is not present it should retuern as zero or null

如果tbl_watch没有显示，我想要得到总的记录，它应该是零或null。

Output:

输出:

+----+--------+--------+
| id | name   |watched |
+----+--------+--------+
| 1  | waheed |  90    |
+----+--------+--------+
| 2  | fareed |  null  |
+----+--------+--------+

How can i get this kind of result.?

我怎样才能得到这样的结果呢?

3 个解决方案

#1

Use LEFT JOIN

使用左连接

Select t1.id , t1.name, count(*) as total from tbl_users as t1 
left join tbl_watched as t2 on t1.id=t2.id_watched
group by t1.id , t1.name

#2

You can do that with a LEFT JOIN between users and watched. That will preserve all rows in the left table and will assign NULL if no matching record is available on the right side. On this result set you can GROUP BY the user's name and count how many things each one has seen.

您可以在用户和监视之间使用左连接进行操作。这将保留左表中的所有行，如果右侧没有匹配的记录，则将分配NULL。在这个结果集上，您可以根据用户的名称进行分组，并计算每个人看到了多少东西。

select  t1.name, count(t2.id)
from    tbl_users t1
left join
        tbl_watched t2
on      t1.id = t2.watched_id
group by t1.name

#3

You can join the tables. I'm not sure about the id_watched column there, assuming its a foreign key from another table. Created a sample schema and query for you at SqlFiddle. Please notice that the third person does not have any watched content, so the count will be 0 in this case. See here.

你可以加入表格。我不确定那里的id_watch列，假设它是另一个表中的外键。创建了一个示例模式并在SqlFiddle中为您查询。请注意，第三人没有任何观看内容，所以在本例中计数为0。在这里看到的。

Sample Schema:

示例模式:

create table tbl_users (
  id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(266) NOT NULL
);

create table tbl_movies (
  id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(266) NOT NULL
);

create table tbl_watched (
  id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id int(8) UNSIGNED,
  movie_id int(8) UNSIGNED,
  FOREIGN KEY (user_id) REFERENCES tbl_users(id),
  FOREIGN KEY (movie_id) REFERENCES tbl_movies(id)
);

INSERT INTO tbl_users (name) VALUES 
("John Doe"),("Jane Doe"),("Jamie Fox");

INSERT INTO tbl_movies (name) VALUES 
("The Pianist"),("Django Unchained"),("Pulp Fiction"),("Wanted");

INSERT INTO tbl_watched (user_id,movie_id) VALUES 
(1,1),(1,2),(1,3),(2,3),(2,4);

Sample query:

查询样例:

SELECT u.*
     , COUNT(w.id) watched_count 
  FROM tbl_watched w
 RIGHT 
  JOIN tbl_users u
    ON u.id = w.user_id
 GROUP  
    BY u.id;

#1