March 16, 2012 at 9:04 am
Hi folks,
I have two queries to get results from two different data source, there are lots of discrepancies between them, the query would be:
select code, [name], [Description], o.descriptionshort as Dept, m.firstname as OwnerFirstName, m.lastname as OwnerLastName
from table0 i
inner join table1 m on m.orgunitid = i.orgunitid
inner join table2 o on o.orgunitid = m.orgunitid
order by code
and
SELECT Distinct AppCode As Code, AppName As [Name], [Description], ClientOwner_Department as Dept, ClientOwner_firstname as OwnerFirstName, ClientOwner_lastname as OwnerLastName
FROM table3
Where AppCode is not NULL
The two results have lots of records with the same "Code" and "Name", however, other fields might be same, might be different, there are also records showing up only in only one query result.
ideally I want to have a single query that will return:
1. Rownumber
2. Code
3. Name
4. Description in first data source
5. Description in second data source
6. Dept in first data source
7. Dept in second data source
8. OwnerLastName in first data source
9. OwnerLastName in second data source
10. OwnerFirstName in first data source
11. OwnerFirstName in second data source
If there are any records in only one data source, I still want it be repeated but show something like "Missing" in the other data source.
I was trying to see if there is such function in Red Gate, it does provide data comparison but unfortunately not for customized query.
Thanks for your help.
March 16, 2012 at 9:07 am
Is the join between the two result sets on CODE and NAME?
March 16, 2012 at 9:15 am
Do a "Full Outer Join" between the two, on Name and Code, with whatever rules you need for other columns.
Something like:
SELECT *,
CASE WHEN Q1.NAME IS NULL THEN 'Q1 Missing'
WHEN Q2.NAME IS NULL THEN 'Q2 Missing'
WHEN CHECKSUM(Q1.DESCRIPTION, Q1.Dept, Q1.OwnerFirstName, Q1.OwnerLastName) != CHECKSUM(Q2.DESCRIPTION,
Q2.Dept,
Q2.OwnerFirstName,
Q2.OwnerLastName)
THEN 'Different'
ELSE 'Matched'
END AS [Comparison]
FROM (SELECT code,
[name],
[Description],
o.descriptionshort AS Dept,
m.firstname AS OwnerFirstName,
m.lastname AS OwnerLastName
FROM table1 i
INNER JOIN table2 m
ON m.orgunitid = i.orgunitid
INNER JOIN table3 o
ON o.orgunitid = m.orgunitid) AS Q1
FULL OUTER JOIN (SELECT DISTINCT
AppCode AS Code,
AppName AS [Name],
[Description],
ClientOwner_Department AS Dept,
ClientOwner_firstname AS OwnerFirstName,
ClientOwner_lastname AS OwnerLastName
FROM table4
WHERE AppCode IS NOT NULL) AS Q2
ON Q1.NAME = Q2.Name
AND Q1.Code = Q2.Code
ORDER BY ISNULL(Q1.code, Q2.code),
ISNULL(Q1.NAME, Q2.Name) ;
Edit: Table names modified as per request.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2012 at 9:30 am
Thanks.
Why adding ROW_NUMBER() OVER (ORDER BY Q1.Code ASC) AS 'Row Number' gives me Row Number starting 900 and not in order?
March 16, 2012 at 1:03 pm
If the query results are from two different data sources and you cannot do a JOIN, this tool will show you all the differences quite easily:
March 16, 2012 at 1:06 pm
halifaxdal (3/16/2012)
Thanks.Why adding ROW_NUMBER() OVER (ORDER BY Q1.Code ASC) AS 'Row Number' gives me Row Number starting 900 and not in order?
because there is no default order. SQL built the row number as required, but of course there's no obligation by SQl to return them in any order unless you tell it so.
if you add ORDER BY 'Row Number' , you'll get them in the order expeded
Lowell
March 20, 2012 at 9:30 am
Thanks to all replies, much appreciated.
March 20, 2012 at 3:18 pm
I am sorry this post should not be closed yet, after some data verification, it turns out the query might need some amendment:
In MANY records showing "Table 1 is missing", actually they do exist! and so as "Table 2 missing"
March 21, 2012 at 4:56 am
halifaxdal (3/20/2012)
I am sorry this post should not be closed yet, after some data verification, it turns out the query might need some amendment:In MANY records showing "Table 1 is missing", actually they do exist! and so as "Table 2 missing"
That just means you need to correct the join between the queries/tables. Nobody here has your tables, nor the data in them, so it's going to be impossible for any of us to correct that. Fix the join definitions, and you'll get what you need.
Or provide table definitions (Create Table scripts), and sample data (Insert statements) that we can run in test databases, and then we can help more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply