在mysql中选择语句存储过程

I have three tables in database:

我在数据库中有三个表:

user_tbl: userID | name | email | password
task_tbl: taskID | description | Date
usertask_tbl: userID | taskID

Now i want to display all the tasks related to one user, it should go and get check the taskid of that user and show all the description and date for that user. But instead its showing one description and date.

现在我想显示与一个用户相关的所有任务,它应该去检查该用户的taskid并显示该用户的所有描述和日期。但相反,它显示了一个描述和日期。

Here is my stored procedure:

这是我的存储过程:

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

I'm new to this. If this is not the right way to do it please help me.

我是新手。如果这不是正确的方法,请帮助我。

1 个解决方案

#1

You are only selecting one task when you do:

在执行以下操作时,您只选择一项任务:

SELECT @TaskID :=taskID from usertask_tbl WHERE userID = @userID;

Therefore, instead of:

因此,而不是:

SELECT @TaskID :=taskID from usertask_tbl WHERE userID = @userID;
SELECT description, Date from task_tbl WHERE taskID = @TaskID;

Try:

SELECT description, Date from task_tbl AS t1
INNER JOIN usertask_tbl AS t2 ON t1.taskID = t2.taskID
WHERE userID = @userID;

#1