CODEWITHSUNDEEP
mysqlsqldatabaseinner-join
user17
user17

Reputation: 383

How to write complex query with mysql join with tables

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

Upvotes: 1

Views: 2360

Answers (4)

John Woo
John Woo

Reputation: 263693

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

Demo: http://sqlfiddle.com/#!2/a22d0/7

Upvotes: 1

xkeshav
xkeshav

Reputation: 54016

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

Upvotes: 0

lc.
lc.

Reputation: 116458

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

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

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

Upvotes: 0

Related Questions