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)
精彩评论