Solved

SQL NOT EXISTS

Posted on 2013-06-28
17
258 Views
Last Modified: 2013-07-01
Hi Experts,

I am trying to display news items to users that have been added since they last visited. As they view them, the list of news items should reduce accordingly i.e. if there are three new items and they view one, two should remain and so on.

As a user clicks on an item to read it I record the following in a table [uservisits]:
datetime - as (lastvisit)
unique id of newsitem - as (itemid)
unique user id - as (userid)
and to distinguish news from any other item a newsid is stored too - as (appid)

CRUCIALLY - and this may be where I am going wrong I have one entry in my uservisits table that stores a lastvisit date for each user for news but has a null value for item id - this seemed to be neccessary to establish the initial relationship between the two - because if there is no lastvist date recorded for news at all how can you display everything since lastvisit? If there is a way I'd becurious to know it...

In contrast each item in the news table has the following key data:

unique id (NewsID)
date added (PostDate)
appid which is the same for each item - so all news has an appid of 16 whereas events might all be 10 etc

So I have been trying to achieve this is by selecting all news where PostDate > lastvisited and appid = appid (that gets me all the news items since the last of the lastvisited dates for any news item - ahh i'm beggining to see my own mistake - minus (NOT EXISTS) select all news items where a record exists in the lastvisited table where appid = appid AND NewsID = itemid so this should now filter out from my original list of new items since lastvisited any items where the id matches that of the new items and presumably theirspecific lastvisited dates are not considered in the overall select - except I think they are being and thats my problem because then as soon as I read one I have added a lastvisit date that is > all of the postdates...

Here's the SQL:

SELECT n.NewsID, n.Heading, n.PostDate, n.MainImage, n.appid, v.appid AS Expr1
FROM 
News AS n INNER JOIN
uservisits AS v 
ON n.appid = v.appid AND n.PostDate > v.lastvisit
WHERE        
(v.appuser = 6) 
AND 
(NOT EXISTS
(SELECT n.NewsID, n.Heading, n.PostDate, n.MainImage
FROM           
News AS n INNER JOIN
uservisits AS v 
ON n.appid = v.appid
WHERE        
(n.NewsID = v.itemid) 
AND (v.appuser = 6) 
))

Open in new window


Many thanks in advance
0
Comment
Question by:forsters
  • 8
  • 6
  • 3
17 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39283978
>> if there is no lastvist date recorded for news at all how can you display everything since lastvisit? If there is a way I'd be curious to know it...

default an old date?

MySQL
ifnull(v.lastvisit,'1900-01-01')

MSSQL
isnull(v.lastvisit,'19000101')

what dbms is this by the way?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39283994
this may be completely wrong, but I'm assuming that itemid is null only if there has been no visit, after the first visit it is present, so just wondering if this might work?
SELECT
       n.NewsID
     , n.Heading
     , n.PostDate
     , n.MainImage
     , n.appid
     , v.appid AS Expr1
FROM uservisits AS v
LEFT JOIN News AS n ON (
                            v.appid = n.appid 
                        AND n.PostDate > v.lastvisit
                        AND v.itemid IS NOT NULL
                        )
                       OR
                        (
                        v.itemid IS NULL
                        )
WHERE v.appuser = 6

Open in new window

0
 

Author Comment

by:forsters
ID: 39284249
Hi thanks for your comments,

re. below comment:
but I'm assuming that itemid is null only if there has been no visit, after the first visit it is present,

No, not exactly, there is one row per user, per 'app' e.g. news than has a lastvisit value but no itemid but this row is permenant it marks the 'start from this date forward' if you like since there are 6-7yrs of old news items pre 06/2013 that I want to effectively ignore.
 So as a user then reads any of their new news items each one read generates a new row in uservisits once, any subsequent re-reads of the same news item just updates the lastvisited date for that row. The row with the null value for itemid remains unchanged it's just there to make that initial association between there being news items with a date and people having read them or not.

I could as you suggest just set a start date but i'm not sure then how to start the process as it were, my uservisits table would be empty...

I'm in MSSQL - sorry should have made that clear
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284342
ok, thanks, so multiple records in uservisits, one has a null itemid (and it remains there)
- but only to 'seed' a first visit it seems (?) - with all available news

additional records get appended once the user visits a news topic area (appid) & (itemid)
and, if the appid is present, you want that to restrict news

I think this might work (cannot test of course, hint sample data is useful)
SELECT
       n.NewsID
     , n.Heading
     , n.PostDate
     , n.MainImage
     , n.appid
     , v.appid AS Expr1
FROM uservisits AS v
LEFT JOIN News AS n ON (
                            v.appid = n.appid 
                        AND n.PostDate > v.lastvisit
                        )
                       OR
                        (
                         v.itemid IS NULL
                         AND NOT EXISTS (
                                           select 1 
                                           from uservisits 
                                           where itemid is not null 
                                           and appuser = v.appuser 
                                        )
                        )
WHERE v.appuser = 6

Open in new window

0
 

Author Comment

by:forsters
ID: 39284413
Sorry, sample data below:

USERVISITS
ID       appid        itemid      appuser     lastvisit
32      16            NULL             6      23/06/2013 00:00:00
33      16            1020              6       28/06/2013 09:45:16


NEWS
NewsID    PostDate            appid
1020        25/06/2013            16
1023        25/06/2013            16
1024        27/06/2013            16
1025        27/06/2013            16

So based on above data user No.6 had 4 new items of news to read since the start of 23/06/2013, they viewed news item 1020 today so the list should now show them the remaining 3 items newsID 1023, 1024 and 1025 - what I think is happening with my original query is that because the lastvisit date for itemid 1020 exceeds the postdate for all the news items it drops them too....
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284592
for the sample data, to arrive at "now show them the remaining 3 items newsID 1023, 1024 and 1025" then the postdate will suppress the result. The following will produce that expected result:
SELECT
      n.NewsID
	           --, n.Heading
	, n.PostDate
    , v.lastvisit
	          --, n.MainImage
	, n.appid
	, v.appid AS Expr1
FROM News AS n
INNER JOIN uservisits AS v ON n.appid = v.appid
	AND ( 
         n.NewsID <> v.itemid -- AND n.PostDate > v.lastvisit
        )
WHERE (v.appuser = 6)

Open in new window

NEWSID	POSTDATE	LASTVISIT	APPID	EXPR1
1023	25/06/2013	28/06/2013 09:45:16	16	16
1024	27/06/2013	28/06/2013 09:45:16	16	16
1025	27/06/2013	28/06/2013 09:45:16	16	16

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284600
feedback? what next?

btw: should lastvisit have the time truncated to compare better to postdate?
0
 

Author Comment

by:forsters
ID: 39284627
I actually don't follow your previous...

the postdate will suppress the result, meaning it's not possible?

btw: should lastvisit have the time truncated to compare better to postdate

I will likely do the opposite and change the Postdate to pickup a time as well - for now I'm not bothered as just trying to satisfy myself that it's even possible
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Expert Comment

by:DOSLover
ID: 39284724
Based on your explanation (and PortletPaul's questions), I think existence of null itemId in UserVisits table should not affect how we pull the data. We can pull records from News table if we don't find a matching UserVisits record based on appid and itemid and that has a lastvisitdate greater than postdate as follows.:
SELECT n.NewsID, n.Heading, n.PostDate, n.MainImage, n.appid
  FROM News n 
 WHERE NOT EXISTS
(SELECT v.appId
   FROM uservisits v 
  where n.appid = v.appid
    AND n.NewsID = v.itemid
    AND n.PostDate > v.lastvisit
	AND v.appuser = 6
 )

Open in new window

0
 

Author Comment

by:forsters
ID: 39284812
Hi thank you,

Unfortunately that brings everything in the news table back that doesn't have a corresponding row in uservisits. I only want new items e.g. items later than the lastvisit date in the NULL itemid row of uservisits but excluding any items the user then reads i.e. any rows in the uservisits table that have an itemid that matches one of the new news id's - the given being that the appid is 16 (news) and the appuser is 6 (me)

So the null itemid row in uservisits is there to effectively say only bother with news items where the postdate is later than this.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39284957
try this please
SELECT
       n.NewsID
     , n.Heading
     , n.PostDate
     , v.lastvisit
     , n.MainImage
     , n.appid
     , v.appid AS Expr1
FROM News AS n
INNER JOIN uservisits AS v ON ( n.appid = v.appid and v.itemid is null )
WHERE v.appuser = 6
AND n.postdate > v.lastvisit
AND NOT EXISTS (
                    SELECT NewsID
                    FROM News AS R
                    INNER JOIN uservisits AS u ON r.appid = u.appid
                                              AND u.itemid is NOT null
                                              AND r.NewsID = u.itemid
                    WHERE u.appuser = v.appuser
                    and r.newsid = n.newsid
                    )

Open in new window

0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39285037
My bad. I didn't realize it was pulling everything without a join. Here is the change: For appuser 6, give me news items where we have in uservisits a null itemid record for the appId with a postdate greater than lastvisitdate and also no uservisits records greater than postdate that match appid and itemid:

SELECT n.NewsID, n.Heading, n.PostDate, n.MainImage, n.appid
  FROM News n 
  JOIN UserVisits v ON Where n.appId = v.AppId 
 where v.appuser = 6
   and EXISTS
(SELECT v1.appId
   FROM uservisits v1 
  where n.appid = v1.appid
    and v1.appuser = v.appuser
    AND v1.itemid is NULL
    AND n.PostDate > v1.lastvisit
 )
    AND NOT EXISTS
(SELECT v1.appId
   FROM uservisits v1 
  where n.appid = v1.appid
    and v1.appuser = v.appuser
    AND n.NewsID = v1.itemid
    AND n.PostDate > v1.lastvisit
 )

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285084
sorry forgot this, http://sqlfiddle.com/#!3/62877/1
results of code at ID: 39284957
NEWSID	HEADING	POSTDATE	LASTVISIT	MAINIMAGE	APPID	EXPR1
1023	(null)	25/06/2013	23/06/2013 	(null)		16	16
1024	(null)	27/06/2013	23/06/2013 	(null)		16	16
1025	(null)	27/06/2013	23/06/2013 	(null)		16	16

Open in new window


@DOSLover, there's a stray 'where' in line 3 above (it happens)
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39285096
Thanks, PortletPaul, for pointing that out. Here is the corrected code
SELECT n.NewsID, n.Heading, n.PostDate, n.MainImage, n.appid
  FROM News n 
  JOIN UserVisits v ON n.appId = v.AppId 
 where v.appuser = 6
   and EXISTS
(SELECT v1.appId
   FROM uservisits v1 
  where n.appid = v1.appid
    and v1.appuser = v.appuser
    AND v1.itemid is NULL
    AND n.PostDate > v1.lastvisit
 )
    AND NOT EXISTS
(SELECT v1.appId
   FROM uservisits v1 
  where n.appid = v1.appid
    and v1.appuser = v.appuser
    AND n.NewsID = v1.itemid
    AND n.PostDate > v1.lastvisit
 ) 

Open in new window

0
 

Author Comment

by:forsters
ID: 39289305
PortletPaul you're a genius that seems to have nailed it, thank you so much, clever solution.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289313
:) thanks, too kind. btw, line 17

AND u.itemid is NOT null

is redundant, line 18 goes on to prove this - sorry it was a carry-over from an earlier attempt.

By way of explanation, the record without the itemid is convenient in being a single record per user, so it does not multiply the news table rows when joined together. This then gives a "baseline lastvisit" to help evaluate the news items against. The rest just knocks out news items already read.
0
 

Author Comment

by:forsters
ID: 39289319
Ah thank you I will amend.

It's brill I love it, had been thinking over the weekend that I would need to pull in a third table but you've come up with a great solution, I wouldn't have seen it for looking.

Thanks again

P.S. I may be back here in a moment as having got this part working I now realise a similar but different query is not quite right...watch this space!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now