problem in sql query

Question

Hey guys i am really messing up with this. I tried this but failed. I have following three tables with user polard searched 2 times at different days, with increased friends in second search in table b. I just need to build the query which will give me the two newly added friends (wont be present in end of list of second search). In table c there will be 20 friends of polard displayed with usid 1 and then 22 friends with usid 2 means total records will be 42.

table a               
uid  name
1     karos
2     polard

table b                              
usid  umid name    friends
1      2  polard    20
2      2  polard    22

table c 
usrid   usid  name   friends
 1       1    hansy  10
 2       1    boje   23
 .       .     .      .
 .       .     .      .   

your help will be geatly appreciated. Thank you in advance.

Answer 1

You can use a subquery using NOT IN

following the example in http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm, you can say, give me all friends of user id 2, NOT in (friends of user id 1)

Answer 2

Is this what you are looking for?

SELECT usrid 
FROM c 
WHERE usid = 2 
AND usrid NOT IN (SELECT usrid FROM c WHERE usid = 1) 

If you want both the usid values to be dynamic as well, assuming that you need to do for the latest usid for polard, it might be something like

SELECT usrid 
FROM c 
WHERE usid = 
       SELECT MAX (usid) FROM b WHERE name = 'polard') 
AND usid NOT IN 
      (SELECT usrid FROM c WHERE usid = 
           SELECT MAX (usid) - 1 FROM b WHERE name = 'polard')) 

This is assuming that the usid values are integer incrementing values specific for each umid in table b.

If they arent, then it will be a bit more complicated as you need to do an Order by and get the 2nd to last umid value