select for not match data

Last Post 13 Sep 2007 12:39 PM by roma. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
roma
New Member
New Member

--
13 Sep 2007 11:57 AM
This select doesn't work for me properly.
I need to find not matched accoutn numbers between Customers and Activity tables.
Where is my mistake:

SELECT Activity.*
FROM Activity INNER JOIN
ProcessState ON Activity.ActivityID = ActivityProcessState.ActivityID INNER JOIN
ActivityTypeState ON Activity.ActivityID = ActivityTypeState.ActivityID
WHERE (Activity.AccountNumber IS NOT NULL OR Activity.AccountNumber <> '')
AND (ActivityTypeState.TypeIdentifier = 'Cancel')
AND (Activity.AccountNumber NOT IN
(SELECT accountnumber
FROM customers
WHERE accountnumber IS NOT NULL))
ORDER BY Activity.ActivityID DESC
roma
New Member
New Member

--
13 Sep 2007 12:39 PM
Yes, this table doesn't have relationship, that is why i can't use cusotomerIDs
SwePeso
New Member
New Member

--
19 Sep 2007 05:59 AM
SELECT Activity.*
FROM Activity
INNER JOIN ProcessState ON Activity.ActivityID = ProcessState.ActivityID
INNER JOIN ActivityTypeState ON ActivityTypeState.ActivityID = Activity.ActivityID
WHERE Activity.AccountNumber > ''
AND ActivityTypeState.TypeIdentifier = 'Cancel'
AND NOT EXISTS (SELECT * FROM Customers WHERE Customers.AccountNumber = Activity.AccountNumber)
ORDER BY Activity.ActivityID DESC
SwePeso
New Member
New Member

--
19 Sep 2007 05:59 AM
SELECT Activity.*
FROM Activity
INNER JOIN ProcessState ON ProcessState.ActivityID = Activity.ActivityID
INNER JOIN ActivityTypeState ON ActivityTypeState.ActivityID = Activity.ActivityID
WHERE Activity.AccountNumber > ''
AND ActivityTypeState.TypeIdentifier = 'Cancel'
AND NOT EXISTS (SELECT * FROM Customers WHERE Customers.AccountNumber = Activity.AccountNumber)
ORDER BY Activity.ActivityID DESC
You are not authorized to post a reply.

Acceptable Use Policy