Knotty query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Knotty query

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-10-17 : 15:10:18

I have 2 tables as follows:

Table A

ParentID ObjectID
----------------------
12345 20009
12345 20010
12345 20011
12345 35454
22222 34343
22222 34355
33333 98766
33333 98767

etc....

and another table as follows:

PackageID ObjectID
-------------------------------
TEST 20009
TEST 20010
TEST 20011
TEST 35454
TEST100 34343
TEST100 34355
TEST100 44444
TEST200 98766

etc...

I need a query that will give me a listing of all the differences between the two tables on a Parent ID / Package ID basis.

In other words I would want the following output for the above data:

ParentID PackageID ObjectID Reason
----------------------------------------------------------------------
NULL TEST100 44444 Record does not exist in Table A
33333 NULL 98767 Record does not exist in Table B

Basicallty, each object in both tables should have the same Parent ID / Package ID, so in the above example Parent ID 12345 is fine as its Object ID's are the same set as the Object ID's for the Package ID "TEST".

I do not have any data mapping ParentID's to PackageID's.

I have started looking at this with a cursor but have got into a knot and find it very ugly.

Is there an easy way of diong this without a cursor?

Thanks in advance,
Kabir

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 15:39:17

Sounds like you need a FULL OUTER JOIN:


DECLARE @Table1 TABLE
(
	ParentID int, 
	ObjectID int
)


DECLARE @Table2 TABLE
(
	PackageID varchar(10), 
	ObjectID int
)

INSERT INTO @Table1
SELECT 12345, 20009 UNION ALL
SELECT 12345, 20010 UNION ALL
SELECT 12345, 20011 UNION ALL
SELECT 12345, 35454 UNION ALL
SELECT 22222, 34343 UNION ALL
SELECT 22222, 34355 UNION ALL
SELECT 33333, 98766 UNION ALL
SELECT 33333, 98767

INSERT INTO @Table2
SELECT 'TEST', 20009 UNION ALL
SELECT 'TEST', 20010 UNION ALL
SELECT 'TEST', 20011 UNION ALL
SELECT 'TEST', 35454 UNION ALL
SELECT 'TEST100', 34343 UNION ALL
SELECT 'TEST100', 34355 UNION ALL
SELECT 'TEST100', 44444 UNION ALL
SELECT 'TEST200', 98766

SELECT	[ObjectID] = COALESCE(T1.ObjectID, T2.ObjectID), 
	[1_ParentID] = T1.ParentID, [2_PackageID] = T2.PackageID,
	[Message] = CASE WHEN T1.ObjectID IS NULL THEN 'Record does not exist in Table A'
			WHEN T2.ObjectID IS NULL THEN 'Record does not exist in Table B'
			ELSE 'Matched'
			END
FROM	@Table1 AS T1
	FULL OUTER JOIN @Table2 AS T2
		ON T1.ObjectID = T2.ObjectID
--
WHERE	T1.ObjectID IS NULL OR T2.ObjectID IS NULL

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 16:19:36

No need for a FULL OUTER JOIN. Just use UNION:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 16:29:05

Is the FULL OUTER JOIN "bad" in this context Jeff? Or is it much-of-a-muchness ??

Thanks

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 16:52:55

I think they are always bad, but that's just my opinion. I recommend to avoid them in general and I outline my case here:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-17 : 17:07:46

For grins, here is a way to do it with UNION as Jeff suggested.

SELECT 
	ObjectID, 
	MAX(ParentID) AS ParentID, 
	MAX(PackageID) AS PackageID,
	CASE
		WHEN MAX(ParentID) IS NULL 
			THEN 'Record does not exist in Table A'
		WHEN MAX(PackageID) IS NULL
			THEN 'Record does not exist in Table B'
		ELSE NULL
	END AS [Message]
FROM
(
  SELECT 
		ObjectID, 
		ParentID, 
		NULL AS PackageID
  FROM 
		@Table1

  UNION ALL

  SELECT 
		ObjectID, 
		NULL, 
		PackageID
  FROM 
		@Table2
) tmp
GROUP BY 
	ObjectID
HAVING 
	COUNT(*) = 1

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-10-19 : 13:18:59

Thanks for that.

I used an outer join and it worked a treat.

However, I now need to refine things further.....

Basically, I need to do the following also:

In instances where an ObjectID exists in Table A but does not exist in Table B I would only like to report the record if the ParentID of that record has matched with at least one record in Table B.

For example, if TableA is as follows:

ParentID ObjectID
----------------------
9999 1
9999 2
9999 3
8888 100

and TableB is as follows

PackageID ObjectID
----------------------
TEST 1
TEST 2
TEST 4
TEST2 200

I would want the output to be:

ParentID PackageID ObjectID Reason
----------------------------------------------------------------------
9999 TEST 3 Record does not exist in Table B
9999 TEST 4 Record does not exist in Table A

But I do not want the following records to be output

ParentID PackageID ObjectID Reason
----------------------------------------------------------------------
8888 TEST2 100 Record does not exist in Table B
8888 TEST2 200 Record does not exist in Table A

This is because none of the ObjectID's associated with ParentID 8888 have a single match with a record in TableB.

i.e. ObjectID 100 does not match with an ObjectID in TableB.

I also want to populate all the fields in the output. How would I go about populating the PackageID for the following record?

ParentID PackageID ObjectID Reason
----------------------------------------------------------------------
9999 TEST 3 Record does not exist in Table B

Thanks,
Kabir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources