Confusing results - 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

General SQL Server Forums

New to SQL Server Programming

Confusing results

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-08-16 : 23:23:43

I am selecting the following data. Please note that the "sequence" column is unique key.

SELECT
[sequence]
,[src_gift_customer_number]
,[customer_id]
,[customer_default_contact_id]
,[customer_default_address_id]
,[src_gift_order_number]
,[src_gift_fund_name]
-- ,[appeal_id]
-- ,[appeal_name]
,[src_gift_appeal_name]
,[src_gift_pledge_date]
,[src_gift_pledge_amount]
,[src_gift_data1]
,[src_gift_additional_information]
into [IPO].[dbo].[IPODonations02]
FROM [IPO].[dbo].[IPODonations01]
inner join IPOTest.dbo.ts_customer on customer_reference_code = src_gift_customer_number
order by sequence

Here is the problem. I have spent more than 2 hours on this.

select * from [LPODonations01] returns 3851 rows.
select * from [LPODonations02] returns 3847 rows.

After the above script (joining with a customer data to get the additional customer details), I was able to conclude 4 rows failed because it would not find the corresponding customer record in IPOTest.dbo.ts_customer.

So, i wanted to get the list of records that failed during the above script.

So I wrote this script.

select * from [LPO].[dbo].[LPODonations01]
where sequence not in
(select sequence from [LPO].[dbo].[LPODonations02])

I was expecting 4 records in the result, but it returned 12 records.

How is that even possible. Again, please note that column sequence is unique.
This is driving me crazy. Can any one help?

Thanks,
Shiyam

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-17 : 01:03:23

Hi,

In the select into you are using [IPO].[dbo].[IPODonations02]
while in your difference query you are using.

select * from [LPO].[dbo].[LPODonations01]
where sequence not in
(select sequence from [LPO].[dbo].[LPODonations02])

The database are different.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources