Hi. I would like to compare results of a query to highlight differences in the data between a production database and a database restored from backup.
My queries are as follows.
Any help much appreciated.
Kind regards,
Phil.
Use ProductionDatabase
Select usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN AppUser_UserGroup ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN UserGroup ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By usergroup.Name ASC
GO
Use RestoredDatabase
Select usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN AppUser_UserGroup ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN UserGroup ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By usergroup.Name ASC
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 30, 2024 at 8:16 am
Did you try:
Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
EXCEPT
Select 'production' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN ProductionDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN ProductionDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By Name ASC
Check: Except
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 30, 2024 at 8:27 am
I would like to compare results
Please define more accurately what you mean by 'compare'. What results are you hoping to obtain?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 30, 2024 at 8:37 am
Hi. Thanks for the responses. By compare I mean there are results returned in RestoredDatabase that are not in ProductionDatabase. I want the query to output values from RestoredDatabase that are not in ProductionDatabase.
Many thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 30, 2024 at 8:41 am
Hi Johan Bijnens. Based on my response to Phil EXCEPT does not return the results I am looking for.
<object id="__symantecMPKIClientMessenger" style="display: none;" data-supports-flavor-configuration="true" data-extension-version="1.2.0.158"></object>__PRESENT
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 30, 2024 at 9:35 am
Hi Johan Bijnens. Based on my response to Phil EXCEPT does not return the results I am looking for.
<object id="__symantecMPKIClientMessenger" style="display: none;" data-supports-flavor-configuration="true" data-extension-version="1.2.0.158"></object>__PRESENT
Not sure what all that 'object_id' stuff is.
What results does it return? It should be every restored row which contains one or more differences compared with production.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 30, 2024 at 9:59 am
Hi. After upgrade users were removed from groups. I would like to list only the groups names + users that are no longer listed in the Production database but are listed in the Restored database.
The Production Database returns 4629 rows.
The Restored Database returns 4,799 rows.
I would like a query that returns the 170 records
Currently I have run my query against both then performed review in excel. I concatenated member+group name then did a vlookup. A bit messy. Would be nice to get the results direct from the SQL query.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Something like this?
SELECT DISTINCT
source = 'Restored'
,au1.UserId
,ug1.Name
,au1.UserName
FROM RestoredDatabase.dbo.AppUser au1
JOIN RestoredDatabase.dbo.AppUser_UserGroup auug1
ON au1.UserId = auug1.UserId
JOIN RestoredDatabase.dbo.UserGroup ug1
ON ug1.Groupid = auug1.Groupid
WHERE NOT EXISTS
(
SELECT 1
FROM ProductionDatabase.dbo.AppUser au2
JOIN ProductionDatabase.dbo.AppUser_UserGroup auug2
ON au2.UserId = auug2.UserId
JOIN ProductionDatabase.dbo.UserGroup ug2
ON ug2.Groupid = auug2.Groupid
WHERE au1.UserId = au1.UserId
AND ug1.Name = ug2.Name
AND au1.UserName = au2.UserName
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 30, 2024 at 10:26 am
Thank you very much. That is perfect. I thank you both for responding.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 30, 2024 at 12:17 pm
Glad you already got a solution.
The flaw / bug in my script was that I hard coded "'production' as source,".
If you remove that "source" column from both queries, you'll get the correct results, as "except" compares both result sets and removes rows from the first set that match rows in the second set.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 31, 2024 at 4:20 pm
Did you try:
Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
EXCEPT
Select 'production' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN ProductionDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN ProductionDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By Name ASCCheck: Except
The problem here is that the "source" is different by definition, so this will always produce a "difference" even if everything BUT the source is the same.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2024 at 6:03 am
Johan Bijnens wrote:Did you try:
Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
EXCEPT
Select 'production' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN ProductionDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN ProductionDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By Name ASCCheck: Except
The problem here is that the "source" is different by definition, so this will always produce a "difference" even if everything BUT the source is the same.
Drew
Johan Bijnens wrote:Did you try:
Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
EXCEPT
Select 'production' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN ProductionDatabase.dbo.AppUser_UserGroup
ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN ProductionDatabase.dbo.UserGroup
ON UserGroup.Groupid = AppUser_UserGroup.Groupid
ORDER By Name ASCCheck: Except
The problem here is that the "source" is different by definition, so this will always produce a "difference" even if everything BUT the source is the same.
Drew
Indeed, as I stated in my flaw report answer right above your reply
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply