March 18, 2008 at 8:11 am
Hi guys,
I hope you guys can help me out on this one.
Some background before I begin.
I have a table called uAccountMovements, which contains all the account movement records.
Each Account Movement record has a corresponding Project and UnkownProject. Each of these Projects and UnknownProjects has an Account. Each account can also be assigned to one or more Projects or UnknownProjects (a many-to-many relationship).
I have to mention the data is being imported from an Excel spread sheets. This is the process of importing data:
1.Populate uAccountMovement table with all the account movement records.
2.Populate uProject and uUknownProject referencing a foreign key in uAccountMovement to establish which account movement record the project belongs to.
3.Populate uAccountProject (the junction table for the many-to-many relationship) referencing a foreign key in uProject to establish which project record the account belongs to.
My problem:
When I do a SELECT * FROM uProject it returns 700 rows
When I do a SELECT * FROM uUnknownProject it returns 300 rows
When I do a SELECT * FROM uAccountMovement it returns 1050 rows
Based on the results from the queries above it is clear that there are 50 projects in the uAccountMovement table which has not been populate in either uProject and uUnknownProject. I verified this by looking at the source and established that in fact there are 50 records that needs to in either uProject or uUnknowProject.
What I’m trying to achieve are as follows:
I’m trying to find out exactly which records from the uAccountMovement table has not yet been assigned to either uProject or uUnknownProject which can be identified by a unique OracleProjectId field.
I hope from the query below you can establish what it is I’m trying to achieve:
SELECT AM.OracleProjectId
FROM uAccountMovement AM
LEFT OUTER JOIN uProject P ON AM.OracleProjectId = P.OracleProjectId
WHERE AM.OracleProjectID <> P.OracleProjectID
The above query returns no results at all.
Your help and comments is highly appreciated.
Thanks
R.
March 18, 2008 at 10:25 am
How about the following one?
SELECT AM.OracleProjectId, P.OracleProjectId
FROM uAccountMovement AM
LEFT OUTER JOIN uProject P ON AM.OracleProjectId = P.OracleProjectId
WHERE P.OracleProjectId IS NULL
March 18, 2008 at 10:28 am
When checking for the non-existance of outer joined rows, use the IS NULL comparison instead of the <> (not equals) comparison. The example SQL_ORACLE has given is correct. When outer joining tables, rows that do not exist in the outer table appear as NULL values in the joined recordset.
March 18, 2008 at 11:32 pm
Thanks to both of you for the reply.
Adding the IS NULL did return some records.
What if I want to join a second table to check existence in that table too?
Can I simply add a second LEFT OUTER JOIN like this?:
SELECT AM.OracleProjectId
FROM uAccountMovement AM
LEFT OUTER JOIN uProject P ON AM.OracleProjectId = P.OracleProjectId
LEFT OUTER JOIN uUnknownProject UP ON AM.OracleProjectId = UP.OracleProjectId
WHERE P.OracleProjectId IS NULL AND UP.OracleProjectId IS NULL
This somehow returns the same results as the query you guys suggested above.
Thanks for your help guys.
R
March 19, 2008 at 12:36 am
Hi guys,
Just to update you guys...
I managed to solve this by joining on my primary keys instead.
This is what my query looks like now:
SELECT AM.OracleProjectId
FROM uAccountMovement AM
LEFT OUTER JOIN uProject P ON AM.ID = P.AccountMovementID
LEFT OUTER JOIN uUnknownProject UP ON AM.ID = UP.AccountMovementID
WHERE P.AccountMovementID IS NULL AND UP.AccountMovementID IS NULL
Thanks again for your help.
March 19, 2008 at 8:37 am
I would like to try to replace AND by OR.
March 19, 2008 at 8:58 am
By using AND, you are telling SQL Server that you want to find all uAccountMovement rows that do not exist in either of the other 2 tables. If you use OR, you are looking for uAccountMovement rows that do not exist in one or the other of the 2 outer tables.
March 19, 2008 at 9:36 am
Yeah I know what you guys mean.
But I had to establish were records from uAccountMovements does not exist in neither uProject nor uUnknownProject that’s why I used the AND keyword.
I think my original post had a typo where I stated either instead or neither. 🙂
Sorry, my bad.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply