Sql Server query to compare rows

Hi ,
   I have a table for Documents with following fields
      DocumentNo      
       DocRevision
      Location
      ..............      There are more fields
 
Following is the sample data .
      Doc1, Rev1, Loc1
      Doc1,Rev1, Loc1
     Doc1,Rev1,Loc2
     Doc2,Rev1,Loc2
     Doc2,Rev1,Loc2

   For Given DocumentNo and DocRevision , Location has to be same . In this case Doc1,Rev1 has wrong data .

    I am looking for a Sql server query that can bring   the  wrong data ( In the sample, the query should bring all the three rows of Doc1,Rev1)
Sam OZAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

declare @Documents table
(
	DocumentNo varchar(10),
	DocRevision varchar(10),
	Location varchar(30)
)
insert into @Documents
values
('Doc1', 'Rev1', 'Loc1'),
('Doc1', 'Rev1', 'Loc1'),
('Doc1', 'Rev1', 'Loc2'),
('Doc2', 'Rev1', 'Loc2'),
('Doc2', 'Rev1', 'Loc2');

with cte as
(
	select DocumentNo, DocRevision, Location
	from @Documents
	group by DocumentNo, DocRevision, Location
), cte2 as
(
	select DocumentNo, DocRevision
	from cte
	group by DocumentNo, DocRevision
	having count(*) > 1
)
select a.*
from @Documents a inner join cte2 b
on a.DocumentNo = b.DocumentNo and a.DocRevision = b.DocRevision

Open in new window

0
Sam OZAuthor Commented:
Thanks Chong . My apologies. There is some difference in the DB structure
     Documents  table
         DocumentNo      
        DocRevision
        Location
         ItemNo

  Items Table
       ItemName
       ItemNo

        ItemNo  joins the two tables

      For a given  ItemName,  DocRevision  the Location should be same

      I need to get entries where there are same ItemName ,Docrevision combination  but with different location
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
without your sample data it could only based on my imagination.

try this and see if this provide the expected output?

declare @Documents table
(
	DocumentNo varchar(10),
	DocRevision varchar(10),
	Location varchar(30),
	ItemNo varchar(20)
)
insert into @Documents
values
('Doc1', 'Rev1', 'Loc1', '001'),
('Doc1', 'Rev1', 'Loc1', '001'),
('Doc1', 'Rev1', 'Loc2', '001'),
('Doc2', 'Rev1', 'Loc2', '002'),
('Doc2', 'Rev1', 'Loc2', '002');

declare @Items table
(
	ItemName varchar(30),
	ItemNo varchar(20)
)
insert into @Items
values
('Item Name 1', '001'),
('Item Name 2', '002');

with cte as
(
	select a.DocumentNo, a.DocRevision, a.Location, b.ItemName
	from @Documents a inner join @Items b
	on a.ItemNo = b.ItemNo
	group by a.DocumentNo, a.DocRevision, a.Location, b.ItemName
), cte2 as
(
	select ItemName, DocRevision
	from cte
	group by ItemName, DocRevision
	having count(*) > 1
)
select a.*
from cte a inner join cte2 b
on a.ItemName = b.ItemName and a.DocRevision = b.DocRevision

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.