Help with Join - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Join

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-19 : 12:59:55

I cant seem to figure this query out.

I have 3 tables, I want to loop through all the id's, and update a column in the main table, with query results from 2 other tables.

master_table_staff
id - username - userid - group_result
Example Data
1 - joe1 - 11111 - some_group - 0
2 - joe2 - 11112 - some_group_2 - 0

ad_staff_groups
id - employee_id - employee_group
Example Data
1 - 11111 - some_group
2 - 11111 - some_group_1

hr_staff_groups
id - employee_id - employee_group
Example Data
1 - 11111 - some_group

Results Table Should look, and see that id 11111 has "some_group" in both tables, but some_group_1 is not in both tables, so the result should be 1.
If all groups match in both tables, then the group_result should be a 0, if there are any non matching groups, the result should be a 1.

AFTER QUERY
master_table_staff
id - username - userid - group_result
Example Data
1 - joe1 - 11111 - some_group - 1
2 - joe2 - 11112 - some_group_2 - 0

Here is what I had, but cant figure it out.

UPDATE mt
SET mt.group_result=m.result
FROM master_table_staff mt
INNER JOIN (
SELECT employee_id,CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END AS result
FROM
(
SELECT employee_id,employee_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT employee_id,employee_group,'A' as cat
FROM ad_staff_groups
UNION
SELECT employee_id,employee_group,'B' as cat
FROM hr_staff_groups
)t
GROUP BY employee_id,employee_group
)r
GROUP BY employee_id
)m
ON m.employee_id = mt.ad_staff_id

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-07-20 : 06:44:22

I think first table structure have one missing column Employee group.
Please tell me my guess is correct or not so that i can think more on requirement.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-07-20 : 09:30:49

anyone ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources