problem with 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 2005 Forums

Transact-SQL (2005)

problem with query

Author

Topic

royrfc
Starting Member

1 Post

Posted - 2007-11-15 : 22:09:56

i imports to sheets from excel
the two tables have almost the same information, columns,etc...
i need to validate if the two tables has the same informacion

the first thing i need to do if both tables has the same quantity of rows...
the key column on both table is a varchar

BSIK has 2000 distinct rows
SA_BSIK has 1000 distinct rows

SELECT BELNR FROM BSIK
WHERE
BELNR in (SELECT xref2 FROM SA_BSIK)

return 1000.... perfect

SELECT BELNR FROM BSIK
WHERE
BELNR not in (SELECT xref2 FROM SA_BSIK)

return 0, it supose to return 1000

why may be this happening????
please help

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-15 : 22:33:45

Does bsik.belnr have unique value? How about sa_bsik.xref2?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-16 : 00:07:00

Use these below. The queries are accurate


SELECT distinct BELNR 
FROM BSIK a
WHERE
exists (SELECT * FROM SA_BSIK aa where aa.xref2 = a.Bsik)


SELECT distinct BELNR 
FROM BSIK a
WHERE
not exists (SELECT * FROM SA_BSIK aa where aa.xref2 = a.Bsik)

Here's a sample


IF OBJECT_ID('tempdb..#Tmp1') IS NOT NULL 
Begin
    drop table #Tmp1
End

IF OBJECT_ID('tempdb..#Tmp2') IS NOT NULL 
Begin
    drop table #Tmp2
End

select 1 as id
Into #tmp1
Union all
select 2
Union all
select 3
Union all
select 4
Union all
select 5


select 6 as id
Into #tmp2
Union all
select 7
Union all
select 3
Union all
select 4
Union all
select 5


SELECT distinct ID as [ID's In Both Tables]
FROM #tmp1 a
WHERE
exists (SELECT * FROM #Tmp2 aa where aa.ID = a.ID)


SELECT distinct ID as [ID's not in Tmp2]
FROM #tmp1 a
WHERE
not exists (SELECT * FROM #Tmp2 aa where aa.ID = a.ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources