Combine 2 query's into 1 - 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

Combine 2 query's into 1

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-13 : 12:10:11

So I have 2 querys, that i wanted to try to combine into 1, but cant seem to figure out how to do it.

Here are my tables -
User_Table_One
User_Table_One_Groups

User_Table_Two
User_Table_Two_Groups

Now right now, i do a full outer join on User_Table_One and User_Table_Two to figure out matching records etc. and this works fine...

Here is the query.

SELECT ad.ad_username, ad.ad_first_name, ad.ad_last_name, ad.ad_middle_name, ad.ad_status, ad.ad_description, ad.ad_dn, sis.external_id,sis.first_name,sis.last_name,sis.middle_name,sis.school,sis.user_type,sis.grade,sis.teacher,sis.internet,sis.map_school_code,sis.map_school_ful_name,sis.map_school_type,sis.map_school_type_long,sis.secondary_flag,sis.description,sis.middle_name_short,sis.dn, sis.status, CASE
WHEN ad.ad_username = sis.external_id AND ad.ad_first_name = sis.first_name AND ad.ad_last_name = sis.last_name AND ad.ad_middle_name = sis.middle_name_short AND ad_description = sis.description AND ad.ad_status = sis.status AND ad.ad_dn = sis.dn THEN 0
WHEN ad.ad_username = sis.external_id AND ad.ad_first_name <> sis.first_name OR ad.ad_last_name <> sis.last_name OR ad.ad_middle_name <> sis.middle_name_short OR ad.ad_description <> sis.description OR ad.ad_status <> sis.status OR ad.ad_dn <> sis.dn THEN 1
WHEN ad.ad_username IS NULL THEN 2
WHEN sis.external_id IS NULL THEN 3
END AS result
FROM dbo.ad_students AS ad FULL OUTER JOIN
dbo.sis_students AS sis ON ad.ad_username = sis.external_id

Then, i do another join on the User_Table_Two_Groups and User_Table_One_Groups Tables, to see if the groups match, or if there are inconsistencys.

Here is query -

select student_id, student_group,
CASE WHEN COUNT(DISTINCT cat) = 2 THEN 0 ELSE MAX(cat) END AS result
from
(select student_id, student_group, 1 as cat from ad_students_groups where student_id='136886'
union
select student_id, student_group, 2 from sis_students_groups where student_id='136886'
)t
group by student_id,student_group
HAVING CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END IN (1,2)

Here is my question, is there a way to add a column, maybe called "group_flag" to show me if its a 0, 1 or 2 ?

I dont want to have to run both those queries. I would rather try to get the check the users groups in one swoop?

hope this makes sense?

So right now if i run it will return something like this
data - data - data - data - 0 (result column for full outer join)

I would like somehow to have another column that looks at the groups of the user, and make sure both group tables match, if they do, then put a 0, if they dont put a 1

so it would like like
data - data - data - data - 0 (groups check) - 0 (result column for full outer join)

## EDIT ##
Or is there a single command I can run, to check all the unique users in the users table, and fill in a "groups" column?
- dont know if that is possible since i do the join to do that.
- Or can i run a join, and move that join into a table in 1 query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources