I have tables
Tasks- id,name
then i have
userTasks id , task_id , user_id
and
User - id , name
Suppose i have 10 tasks in task table and out of those i have 3 tasks in userTask table
I want query like this
Select task.id , task.name , STATUS (if(presentInUserTasks),1,0) FROM whatever
the STATUS word should 1 if that task id is present in usertasks table for that userid otherwise it should be 0
So that i am able to find which of those tasks are alreadu in userTask table
You're looking for the EXISTS keyword:
SELECT tasks.id, tasks.name,
IF(EXISTS(SELECT id
FROM userTasks
WHERE userTasks.task_id = tasks.id
AND userTasks.user_id = @that_user_id)
,1,0) AS STATUS
FROM tasks
Try this one:
SELECT b.id,
b.name,
IF(coalesce(c.Task_ID, -1) = -1, 0, 1) `Status`
FROM `User` a
CROSS JOIN `Task` b
LEFT JOIN UserTask c
ON a.ID = c.user_ID AND
b.ID = c.Task_ID
Where a.id = 1
try this:
select T.id , T.name ,
case when u.task_id is not null then 1 else 0 end as STATUS
from
Tasks T left outer join usertasks U
on T.id=u.task_id
TRY
SELECT t.id,t.name,
CASE WHEN ut.task_id IS NULL THEN '0' ELSE '1' END
FROM Tasks t
LEFT JOIN UserTask ut ON ut.task_id = t.id
