I screwed up today and deleted some stuff I shouldn't. I don't want to restore from last night because then I'd have to go through deleting the stuff that should have been deleted again.

I copied a backup back to where I could use it. My confusion starts when I run the following query

select * from contacts where _Status in ('CV on file', 'Placed', 'Hold')

Using the live data I get 643 rows and using the old data its 647

However, when I run

select * from contacts
where
_Status in ('CV on file', 'Placed', 'Hold')
AND
_ID not in (SELECT _ID from live.Contacts)

from the old database I get 7

I then tried

select COUNT(*) from old.contacts
union
select COUNT(*) from live.contacts
union
select count(*) from contacts
where
_ID not in (SELECT _ID from live.Contacts)

and got

12961
12003
962


I'm sure there is a perfectly simple explanation and as soon as someone gives it to me I'll go ahh - its obvious, but I've screwed up enough today I don't want to continue until I know what I'm doing.

Roy Lambert

Roy Lambert wrote:

> select * from contacts where _Status in ('CV on file', 'Placed',
> 'Hold')

What happens when you force an UPPERCASE check:

select * from contacts where UPPER(_Status) in ('CV ON FILE', 'PLACED',
'HOLD')


--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael

>> select * from contacts where _Status in ('CV on file', 'Placed',
>> 'Hold')
>
>What happens when you force an UPPERCASE check:
>
>select * from contacts where UPPER(_Status) in ('CV ON FILE', 'PLACED',
>'HOLD')

I haven't tried but it will/should make no difference since both tables are identical in structure (one's just a few days older), and have the column defined as ANSI_CI

Roy

Roy

I will try to help ... not sure how much!

>>Using the live data I get 643 rows and using the old data its 647

>>select COUNT(*) from old.contacts
>>union
>>select COUNT(*) from live.contacts
>>union
>>select count(*) from contacts
>>where
>>_ID not in (SELECT _ID from live.Contacts)

>>and got

>>12961
>>12003
>>962

12003 + 962 - 12961 = 4

and

647 - 643 = 4

... so you seem to have 4 items in Old.Contacts which are not in NEW.

If you have an integer based ID field on the tables you can try:

SELECT ID FROM Old.Contacts WHERE NOT (ID IN
(SELECT ID FROM New.Contacts) OR ID IN (SELECT ID FROM Live.Contacts))

That should pinpoint the specific Old.Contacts which are not in either of the other tables. If there are 4 of these you have tied up part of the problem.

Good luck.

Adam

>.. so you seem to have 4 items in Old.Contacts which are not in NEW.

I'm not sure how you reach that conclusion. I would prefer to say that I have 4 rows where this filter (_ID not in (SELECT _ID from live.Contacts)) in the WHERE clause does not identify the gap.

>If you have an integer based ID field on the tables you can try:
>
>SELECT ID FROM Old.Contacts WHERE NOT (ID IN
>(SELECT ID FROM New.Contacts) OR ID IN (SELECT ID FROM Live.Contacts))
>
>That should pinpoint the specific Old.Contacts which are not in either of the other tables. If there are 4 of these you have tied up part of the problem.

Crossed wire somewhere - there are only two tables - the old version and the new (up to date) version aka live version.

Sorry for the confusion.

I just went ahead and restored the ones it said were missing (after backing up the live data). I had no collisions on the _ID column which is the primary key so things should be OK.

Roy Lambert