Hi Satwick,
Let's see if we can make this clearer for you, by breaking it down.
From your original question, you have 2 queries:
SELECT *
FROM tableA a
WHERE pkitem NOT IN
( SELECT fkitem
FROM tableB b
WHERE b.fkitem=a.pkitem
AND b.starttime>=GETDATE()
)
This says get me all the rows from tableA where there is no matching record in tableB with a corresponding foreign key column matching tableA's primary key column AND the tableB record has a starttime
now and in the future. This will return any records in tableA that either do not have a tableB foreign key match at all, or does match to tableB but with a starttime on the tableB record which is in the past.
This is functionally equivalent to Arbi's query:
SELECT *
FROM tableA a
WHERE NOT EXISTS
( SELECT 1
FROM tableB b
WHERE b.fkitem=a.pkitem
AND b.starttime>=GETDATE()
)
Changing the "pkitem NOT IN" to "NOT EXISTS" makes it faster. Also, since you are simply checking for existence, it doesn't matter what you return, hence returning "1" so the query processor doesn't even have to get a column back.
Your second query was:
SELECT *
FROM tableA a
JOIN tableB b
ON b.fkitem=a.pkitem
WHERE b.starttime>GETDATE()
This says get me all the rows from tableA and tableB where each record in tableB contains a corresponding foreign key column matching tableA's primary key column AND the tableB record has a starttime only
in the future. This will return any records in tableA and in tableB where tableA primary key and tableB foreign key match, and a starttime on the tableB record which is in the future. This can include duplicate tableA matches
if there are more than one tableB match, and vice versa.
Do you see now that those are 2 totally different queries, and that is why you get 2 different results?
Now, you said:
"I just need to find rows that are not in tableB which are future ones and insert them in
tableB after disabling the triggers."
Let's think about this for a second. Where is the starttime column located? Is there one in tableA? If not, then you already have records in tableB which are future ones, so why would you insert them again?
If as in your earlier queries, the starttime column is only in tableB, then I think what you mean to say is:
"I just need to find rows in tableB which are in the future that are not in tableA and insert them in
tableA after disabling the triggers."
In that case, here is your query:
INSERT INTO tableA (
pkitem,
<other columns>
)
SELECT b.fkitem,
<other columns>
FROM tableB b
WHERE b.starttime > GETDATE()
AND NOT EXISTS
( SELECT 1
FROM tableA a
WHERE b.fkitem=a.pkitem
)
Now, in your last post you mentioned:
"another thing is I need to check any pkitem already booked for that particular time lets say i have two columns in tableB, TIMEfrom and timeTo"
This would lead me to think there is a starttime column on tableA and not on tableB after all. This would also make more sense since I can't imagine how you could have records in the child table that aren't in the parent table, unless they exist but
with past starttimes. In that case, I think what you mean to say is:
"I just need to find rows in tableA which are future ones that are not in tableB and insert them in
tableB after disabling the triggers."
In that case, here is your query:
INSERT INTO tableB (
fkitem,
timefrom,
timeto,
<other columns>
)
SELECT fkitem = a.pkitem,
timefrom = a.starttime,
timeto = <some value>,
<other columns>
FROM tableA a
WHERE a.starttime > GETDATE()
AND NOT EXISTS
( SELECT 1
FROM tableB b
WHERE b.fkitem=a.pkitem
AND a.starttime BETWEEN b.timefrom AND b.timeto
)
Of course, not knowing the actual structure of your 2 tables makes this harder, but hopefully you now get the idea of how to frame the query. Also, if you are truly using foreign key constraints, then disabling the triggers might not be enough.
Depending on the state your data is in, you may also want to temporarily drop these constraints as well while you are doing the insert. I'm assuming that this is part of some initial data load.
Diane
