Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sikharma13
Starting Member
44 Posts |
Posted - 2013-11-17 : 10:55:16
|
select s.entry_id, sum(case when p.no_eli_educ > 0then case when name_of_schools = 'no facility' then 1 else 0 endelse 0end)from schools sinner join payroll p on s.entry_id = p.entry_idgroup by s.entry_idupdate payroll set number_of_no_schools"query here"where p.no_eli_educ > 0 thanks!VFP9.0 via MySQL 5.0 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-17 : 11:00:36
|
quote: Originally posted by sikharma13 select s.entry_id, sum(case when p.no_eli_educ > 0then case when name_of_schools = 'no facility' then 1 else 0 endelse 0end)from schools sinner join payroll p on s.entry_id = p.entry_idgroup by s.entry_idupdate payroll set number_of_no_schools"query here"where p.no_eli_educ > 0 thanks!VFP9.0 via MySQL 5.0
For MySQL 5.0, this would not be the right forum to ask the question. This is a Microsoft SQL Server forum.Regardless, what you would need is something like this (although I don't think MySQL supports the common table expression quite in the same way). Also, if you have multiple rows in the payroll table for a given entry_id, this may not (or may) be the right query:;with cte as(select s.entry_id, sum(case when p.no_eli_educ > 0then case when name_of_schools = 'no facility' then 1 else 0 endelse 0end) as no_schoolsfrom schools sinner join payroll p on s.entry_id = p.entry_idgroup by s.entry_id)update p set p.number_of_no_schools = c.no_schoolsfrom payroll p inner join cte c on c.entry_id = p.entry_id |
|
|
|
|
|