March 26, 2004 at 12:13 pm
I am by no means a SQL Guru, so I figured I would ask those that are about this...
We are working on importing some old data into a new database, in doing so we have run across a serious lapse in the integrity of the "old" data.
TIA
-Scott
I have the following 2 queries and would like to show those records in the two queries that match.
| SELECT Projects.ProjectName, Tasks.TaskName, Customers.CustomerName, Groups.GroupName | ||||||||||
| FROM Tasks INNER JOIN | ||||||||||
| Projects ON Tasks.ProjectID = Projects.ProjectID AND Tasks.GroupID <> Projects.GroupID INNER JOIN | ||||||||||
| Customers ON Projects.CustomerID = Customers.CustomerID INNER JOIN | ||||||||||
| Groups ON Projects.GroupID = Groups.GroupID | ||||||||||
| ORDER BY Customers.CustomerName, Groups.GroupName | ||||||||||
| SELECT Projects.ProjectName, Tasks.TaskName, Customers.CustomerName, Groups.GroupName, Tasks.GroupID | ||||||||||
| FROM Tasks INNER JOIN | ||||||||||
| Projects ON Tasks.ProjectID = Projects.ProjectID AND Tasks.GroupID <> Projects.GroupID INNER JOIN | ||||||||||
| Customers ON Projects.CustomerID = Customers.CustomerID INNER JOIN | ||||||||||
| Groups ON Tasks.GroupID = Groups.GroupID | ||||||||||
| ORDER BY Customers.CustomerName, Groups.GroupName | ||||||||||
March 29, 2004 at 12:30 pm
As the queries are exactly the same is it being run in 2 different databases on the same serevr? If so then try.
SELECT
DB1.*
FROM
(
SELECT Projects.ProjectName, Tasks.TaskName, Customers.CustomerName, Groups.GroupName
FROM db1..Tasks INNER JOIN
db1..Projects ON Tasks.ProjectID = Projects.ProjectID AND Tasks.GroupID <> Projects.GroupID INNER JOIN
db1..Customers ON Projects.CustomerID = Customers.CustomerID INNER JOIN
Groups ON Projects.GroupID = Groups.GroupID
) DB1
INNER JOIN
(
SELECT Projects.ProjectName, Tasks.TaskName, Customers.CustomerName, Groups.GroupName
FROM db2..Tasks INNER JOIN
db2..Projects ON Tasks.ProjectID = Projects.ProjectID AND Tasks.GroupID <> Projects.GroupID INNER JOIN
db2..Customers ON Projects.CustomerID = Customers.CustomerID INNER JOIN
Groups ON Projects.GroupID = Groups.GroupID
) DB2
ON
DB1.ProjectName = DB2.ProjectName AND
DB1.TaskName = DB2.TaskName AND
DB1.CustomerName = DB2.CustomerName AND
DB1.GroupName = DB2.GroupName
ORDER BY DB1.CustomerName, DB1.GroupName
If on seperate servers you need to setup a linked server for DB2 and added the server name before db2..object.
March 30, 2004 at 6:34 am
They are slightly different, the last JOIN is different.
| Groups ON Projects.GroupID = Groups.GroupID | ||||||||||
| ORDER BY Customers.CustomerName, Groups.GroupName | ||||||||||
Groups ON Tasks.GroupID = Groups.GroupID
ORDER BY Customers.CustomerName, Groups.GroupName
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply