Update column in table - based on join result

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

Update column in table - based on join result

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-13 : 13:23:27

I have 1 table with a few columns

table name - users_table
id - user_name - group_result

table name - groups_1
id - user_name - group_name

table name - groups_2
id - user_name - group_name

i do a full outer join on groups_1 and groups_2 to figure out whats in sync.

my question is, is there a way to fill in the group_result column, with data from the full outer join i did on the groups table?

So for example, for each of the users in the users_table, it should get all ther egroups in groups_1 and groups_2, join them and if they all match fill in the group_result = 0 if something is not correct, or there is a non match somewhere, result = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:26:03

where's sample data and output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-13 : 13:35:55

Here is some sample data

table name - users_table
id - user_name - group_result
data:
1 - joe1 - null
2 - joe2 - null

table name - groups_1
id - user_name - group_name
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 10

table name - groups_2
id - user_name - group_name
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 8

So you see joe1 group all match if you did a join, and joe2 dont match so the result shoudl be 1...

Users table should look like this after query update
data:
1 - joe1 - 0
2 - joe2 - 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:39:39

can group_1,group_2 etc contain duplicate sets with same value for user_name - group_name fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-13 : 13:41:55

Yes so groups_1 and groups_2 could look like this

table name - groups_1
id - user_name - group_name
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 10
4 - joe1 - group 1

table name - groups_2
id - user_name - group_name
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 8
4 - joe1 - group 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:52:10

[code]
[code]SELECT ut.ID,ut.user_name,
CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END
FROM users_table ut
LEFT JOIN (SELECT user_name,group_name,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT user_name,group_name,'A' as cat
FROM groups_1
UNION
SELECT user_name,group_name,'B' as cat
FROM groups_2
)t
GROUP BY user_name,group_name
)r
ON r.user_name = ut.user_name
GROUP BY ut.id,ut.user_name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-13 : 21:50:26

Where does it update the group_result column in the users_table?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 10:44:12

Now here a sample of my data, im not sure if this will work...

My master_table (pushed in from full outer join)
structre like
ad_username - username - join result - group_result

data example

joe1 - joe1 - 0 - 0
joe2 - joe2 - 0 - 0
joe4 - NULL - 1 - 0
NULL - joe5 - 2 - 0

table name - sis_students_groups
id - student_id - student_group
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 10
4 - joe1 - group 1

table name - ad_students_groups
id - student_id - student_group

data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 8
4 - joe1 - group 1

It should look for each record in the master_table, now some of the ad_username columns will be NULL so i dont want it to go crazy lol...

It should look at ad_username in the master_table, check that user id in both sis_students_groups and ad_students_groups tables, to see if they all match, or if one is missing from either table. If the items do not match, then group_result should = 1, if they do match group_result should = 0.

joe1 - joe1 - 0 - 0
joe2 - joe2 - 0 - 1
joe4 - NULL - 1 - 0
NULL - joe5 - 2 - 0

########## Edit ###############33

So i ran this below

SELECT ut.ad_username,
CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END
FROM master_table ut
LEFT JOIN (SELECT student_id,student_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT student_id,student_group,'A' as cat
FROM sis_students_groups
UNION
SELECT student_id,student_group,'B' as cat
FROM ad_students_groups
)t
GROUP BY student_id,student_group
)r
ON r.student_id = ut.ad_username
GROUP BY ut.ad_username

it only gives me a disply of ad_username, and a column with no name, i just dont see where its going to update the group_result column in the master_table based on the results...

maybe i think i explained it wrong??

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 12:36:50

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-14 : 12:48:29

just turn the SELECT into an update


UPDATE ut
SET ut.group_result=m.result
FROM users_table ut
INNER JOIN (
SELECT user_name,CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END AS result
FROM
(
SELECT user_name,group_name,
            CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
            FROM
               (SELECT user_name,group_name,'A' as cat
                FROM groups_1
                UNION 
                SELECT user_name,group_name,'B' as cat
                FROM groups_2
               )t
               GROUP BY user_name,group_name
           )r
GROUP BY user_name
)m
ON m.user_name = ut.user_name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 13:40:24

I cant seem to get that to work, did you see the new strucute above?

UPDATE mt
SET mt.group_result=m.result
FROM master_table mt
INNER JOIN (
SELECT ad_username,CASE WHEN SUM(CASE WHEN Stat=1 THEN 1 ELSE 0 END) >0 THEN 1 ELSE 0 END AS result
FROM
(
SELECT student_id,student_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE 1 END AS Stat
FROM
(SELECT student_id,student_group,'A' as cat
FROM ad_students_groups
UNION
SELECT student_id,student_group,'B' as cat
FROM sis_students_groups
)t
GROUP BY student_id,student_group
)r
GROUP BY student_id
)m
ON m.student_id = mt.ad_username

error message

Msg 207, Level 16, State 1, Line 5
Invalid column name 'ad_username'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'student_id'.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 15:17:41

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:02:15

please replace correct column names in query. i don't know what exactly are your column names (it seems to change with your every new post!). so make sure you replace correct ones in suggestion given.
One thing i can see in your last query is you've used columns ad_username and column_id on outer query but haven't included them in inner queries.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-17 : 14:30:19

I did replace the correct column names, but cant seem to findwhats wrong

My master_table (pushed in from full outer join)
structre like
ad_username - username - join result - group_result

data example

joe1 - joe1 - 0 - 0
joe2 - joe2 - 0 - 0
joe4 - NULL - 1 - 0
NULL - joe5 - 2 - 0

table name - sis_students_groups
id - student_id - student_group
data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 10
4 - joe1 - group 1

table name - ad_students_groups
id - student_id - student_group

data:
1 - joe1 - group 1
2 - joe1 - group 2
3 - joe2 - group 8
4 - joe1 - group 1

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-18 : 20:09:45

anyone?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-19 : 11:37:19

Ive tried fixing my query by the table structre above, but still ant get it to work.

Here is the error:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'ad_username'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'student_id'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-19 : 12:58:59

answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144829

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources