select statement stored procedure in mysql

Question

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.

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.

Edu · Accepted Answer · 2016-05-14 11:35:48Z

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;

answered May 14, 2016 at 11:35

Edu

2,7335 gold badges35 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select statement stored procedure in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related