开发者

Sql friends datatable query

Hey guys, I am really messing up with this. I tried this but it 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.


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


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)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜