我在数据库中有三个表:
user_tbl: userID | name | email | password
task_tbl: taskID | description | Date
usertask_tbl: userID | taskID
现在我想显示与一个用户相关的所有任务,它应该去检查该用户的taskid,并显示该用户的所有描述和日期。但它只显示了一个描述和日期。
这是我的存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `alltask`(IN `useremail` INT(11))
BEGIN
SELECT @userID := userID FROM user_tbl WHERE email=useremail;
SELECT @TaskID :=taskID from usertask_tbl WHERE userID = @userID;
SELECT description, Date from task_tbl WHERE taskID = @TaskID;
END
我是新手。如果这不是正确的方法,请帮助我。
执行此操作时,您只能选择一项任务:
SELECT @TaskID :=taskID from usertask_tbl WHERE userID = @userID;
因此,代替
SELECT @TaskID :=taskID from usertask_tbl WHERE userID = @userID;
SELECT description, Date from task_tbl WHERE taskID = @TaskID;
尝试:
SELECT description, Date from task_tbl AS t1
INNER JOIN usertask_tbl AS t2 ON t1.taskID = t2.taskID
WHERE userID = @userID;