I have tables as under.
我有桌子。
worker ----------------------- ID NAME 1 James 2 Robert 3 David 4 Peter task ----------------------- ID Title 1 Painting 2 Repairing 3 Wiring assoc ------------------------ worker_id task_id 2 1 1 2 1 1
On painting page, I want to display all worker name but with yes / no against painting.
在绘画页面上,我想显示所有工人名称,但是对于绘画,是/否。
I want
------------------------- Worker Can Paint ----------- --------------- James Yes Robert No David Yes Peter No
How to write SQL statement? I have no good effort to show. Please help.
如何编写SQL语句?我没有努力表现出来。请帮忙。
2 个解决方案
#1
1
select w.name, case when sum(t.id is not null) > 0
then 'yes'
else 'no'
end as paint
from worker w
left join assoc a on a.worker_id = w.id
left join task t on a.task_id = t.id and t.title = 'Painting'
group by w.name
#2
0
Try this:
SELECT name,
CASE a.task_id
WHEN 1 THEN 'Yes'
WHEN NULL THEN 'No'
END
FROM worker w
LEFT JOIN assoc a ON
w.ID=a.worker_id AND a.task_id=1;
#1
1
select w.name, case when sum(t.id is not null) > 0
then 'yes'
else 'no'
end as paint
from worker w
left join assoc a on a.worker_id = w.id
left join task t on a.task_id = t.id and t.title = 'Painting'
group by w.name
#2
0
Try this:
SELECT name,
CASE a.task_id
WHEN 1 THEN 'Yes'
WHEN NULL THEN 'No'
END
FROM worker w
LEFT JOIN assoc a ON
w.ID=a.worker_id AND a.task_id=1;