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)
 Another query

Author  Topic 

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2007-06-12 : 12:29:33

Hi,

I have a table as follows:

identity Region [ID]
-------------------------------------
1 RegionA ID1
2 RegionA ID2
3 RegionA ID3
4 RegionA ID4
5 RegionA ID5
6 RegionB ID1
7 RegionB ID2
8 RegionB ID3
9 RegionB ID4

where identity is an identity column.

I have another table as follows:

identity Component
-----------------------------
1 Comp1
1 Comp2
2 Comp1
2 Comp5
3 Comp1
6 Comp1
6 Comp2
7 Comp1
8 Comp1
8 Comp2

I want to write a report that will list all the RegionA records in TableA that differ in their component list with their corresponding component's in RegionB.

So in the above example I would like the record

identity = 2
Region = RegionA
[ID] = ID2

to be output since for RegionA, [ID2] has the components Comp1 and Comp2, whaeras for RegionB, [ID2] only has component Comp1.

I would also like the following record to be output:

identity = 3
Region = RegionA
[ID] = ID3

since for RegionA, [ID3] has the component Comp1, whaeras for RegionB, [ID3] has the components Comp1 and comp2.

I would not like ther following record to be output:

identity = 1
Region = RegionA
[ID] = ID1

since for RegionA and RegionB, ID1 has the same components.

Essentially I am comparing the components for each [ID] for RegionA and RegionB.

I have been able to write the above using a cursor but it is ugly.

Thanks in advance,
Kabir

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-06-13 : 23:58:40
You sure you wouldn't rather tell the whole story of everything that doesn't have a match???


--===== Create some test tables with data provided in the post
-- DROP TABLE TableA
-- GO
CREATE TABLE TableA (SomeID INT PRIMARY KEY CLUSTERED, Region VARCHAR(10), [ID] VARCHAR(4))
INSERT INTO TableA (SomeID, Region, [ID])
SELECT '1','RegionA','ID1' UNION ALL
SELECT '2','RegionA','ID2' UNION ALL
SELECT '3','RegionA','ID3' UNION ALL
SELECT '4','RegionA','ID4' UNION ALL
SELECT '5','RegionA','ID5' UNION ALL
SELECT '6','RegionB','ID1' UNION ALL
SELECT '7','RegionB','ID2' UNION ALL
SELECT '8','RegionB','ID3' UNION ALL
SELECT '9','RegionB','ID4'
GO
-- DROP TABLE TableB
-- GO
CREATE TABLE TableB (SomeID INT, Component VARCHAR(10))
INSERT INTO TableB (SomeID, Component)
SELECT '1','Comp1' UNION ALL
SELECT '1','Comp2' UNION ALL
SELECT '2','Comp1' UNION ALL
SELECT '2','Comp5' UNION ALL
SELECT '3','Comp1' UNION ALL
SELECT '6','Comp1' UNION ALL
SELECT '6','Comp2' UNION ALL
SELECT '7','Comp1' UNION ALL
SELECT '8','Comp1' UNION ALL
SELECT '8','Comp2'
GO
--===== Show everything present on one side that is not in the other
-- or may be missing an individual piece of information
SELECT da.SomeID AS aSomeID, da.Region AS aRegion, da.ID AS aID, da.Component AS aComponent,
db.SomeID AS bSomeID, db.Region AS bRegion, db.ID AS bID, db.Component AS bComponent
FROM
(
SELECT ta.SomeID,ta.Region,ta.ID,tb.Component
FROM TableA ta
LEFT OUTER JOIN
TableB tb
ON ta.SomeID = tb.SomeID
WHERE ta.Region = 'RegionA'
)da
FULL OUTER JOIN
(
SELECT ta.SomeID,ta.Region,ta.ID,tb.Component
FROM TableA ta
LEFT OUTER JOIN
TableB tb
ON ta.SomeID = tb.SomeID
WHERE ta.Region = 'RegionB'
)db
ON da.ID = db.ID
AND da.Component = db.Component
WHERE da.Component IS NULL
OR db.Component IS NULL
ORDER BY da.ID,db.ID



--Jeff Moden
Go to Top of Page
   

- Advertisement -