SQL Query Help....
Hi All,
As part of a one time migration activity, we are running into a situation wherein in the old database, there are some records in the transaction tables that don't have a valid reference in the master table. This was because the OLD database was not having any proper constraints in place, and it is suspected that the records in the Master table would somehow got deleted.
I now what to write a query which will pick only the valid data and the other non matching records should be marked as not valid or so. Bringing a new status called as Not valid in each n every master table is also fine.
Ideally this will be a left join with the master and the transaction tables, but the issue am facing is that the left join is giving only the matching rows, the non matching rows in the master is ignored, how do i tag them the Not Valid status?
thanks,
[895 byte] By [
MShetty] at [2007-12-25]
It sounds like you need to add the NotValid flag to the Transaction table and not the Master table. You have stated that you have Transactions with no Master not Masters with no Transactions. This might also explain why a Left Join from the Master is not producing NotValid rows.
To move the data, you only have to Join the tables normally and you will get valid records.
To find Transactions that are missing Master records, you should probably use a Not Exists query. This could even be used for updating a NotValid flag in the Transaction table.
Without seeing your query, it is hard to tell exactly the situation, but what I think you probably need to do is to move a check from the WHERE clause to the JOIN criteria. In the WHERE clause, if a value is NULL due to being non existant in the outer join, it will be filtered out because anything = NULL results in NULL, which is not TRUE.
So changing:
SELECT
FROM table
LEFT OUTER JOIN table2
on table.value = table2.value
WHERE table2.value = 'literal'
to:
SELECT
FROM table
LEFT OUTER JOIN table2
on table.value = table2.value
and table2.value = 'literal'
Might be the solution to your problem...
Kindly apologize for not detailing out my problem;
What exactly is happening is there are two tables,
UserMaster_Table
Uid Uname
1 Samules
2 Martin
UserTransaction_Table
Uid Result
1 F
3 P
the issue now is, the Second row in the transaction table, when I migrate these data with proper FK constraints (Uid) in this case, the Second row fill fail, what I want to do is, get that row too but tag that row some status which will let me know this row doesn't have a valid Parent or so. Any alteration to be done in the master table like bring in a col or two is perfectly fine.
 MShetty wrote: |
| the issue now is, the Second row in the transaction table, when I migrate these data with proper FK constraints (Uid)... |
|
If you have foreign key constraints enforced in SQL Server you won't be able to insert a child record into the table without the master. Nor should you want to. Rather, I would create a separate child table .. say it's called "UserTransactionOrphans_Table" - which has the exact same structure as the original table, but with no foreign key constraints turned on. So you'd have two imports - one which grabs records that have a parent and puts it in the good table, and the other that grabs orphans and puts it in the other table:
1) Good records
SELECT UserTransaction_Table.* FROM UserTransaction_Table INNER JOIN UserMaster_Table ON UserTransaction_Table.Uid = UserMaster_Table.Uid
2) Bad records
SELECT UserTransaction_Table.* FROM UserTransaction_Table LEFT OUTER JOIN UserMaster_Table ON UserTransaction_Table.Uid = UserMaster_Table.Uid
WHERE UserMaster_Table.Uid IS NULL
--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...
yup what u have mentioned will serve the purpose :)
but what I got in mind is, instead of bringing in one more table, have a row called as Invalid in the master table, and each n every row in the transaction table that is not having a match with the master table, will be tagged this value.
Ok - in that case you're going to need to allow for identity insertion into the master table (assuming your user ID in the master table is an identity seed value).
You should run the second query first to retrieve the list of all missing master IDs. Then create dummy records in the master table for each of those IDs with your flag concept. Then import using the second query to the child table (forgetting the secondary orphan table). Then, import into the master table the data from the old db, and finally import into the child table again using the first query.
Your initial dummy master record filing routine, supposing 'Invalid' is a boolean field, would look something like:
INSERT INTO UserMaster_Table (Uid, Invalid)
SELECT OldUserTransaction_Table.Uid, 1 AS Invalid
FROM OldUserTransaction_Table LEFT OUTER JOIN OldUserMaster_Table
ON OldUserTransaction_Table.Uid = OldUserMaster_Table.Uid
WHERE OldUserMaster_Table.Uid IS NULL
GROUP BY OldUserTransaction_Table.Uid
--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...
Or, upon reading your post more carefully it sounds like you want to have only one master record. So, import the master table and then create the "invalid" master record with its ID. Then, for a moment, turn off foreign key constraints and import the whole child table. Then do a mass update:
UPDATE UserTransaction_Table SET Uid = InvalidMasterID
WHERE UserTransaction_Table.Uid IN
(
SELECT UserTransaction_Table.Uid
FROM UserTransaction_Table LEFT OUTER JOIN UserMaster_Table
ON UserTransaction_Table.Uid = UserMaster_Table.Uid
WHERE UserMaster_Table.Uid IS NULL
GROUP BY UserTransaction_Table.Uid
)
Then turn foreign key constraints back on.
Thx for the Help Tony!!! Will defnitely give it a try..
No problem! If it works, don't forget to close out this thread by marking it as answered.
Let us know how it goes.
--
Tony Alicea
http://www.theabstractionpoint.com
clarity of mind and creativity in application software development...
Hi Tony,
How about this?
the master will have a record like
-1 'InValid'
and then this query
select
isNull(master.UId,-1) from
UserTransaction_Table trans
left
join UserMaster_Table Master on
(
trans.UId = Master.UId) this would be the query I'll run to enter the Invalid flag into the transaction table.
Yes, that will work too, assuming whatever old database you are using supports "isnull". I kind of assumed since you said "old database" that it wasn't in SQL Server (which is why I recommended that you import all the data first, then update). But if the old database is in SQL Server - you should be good to go!
