SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query - Display items user hasnt seen
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teddymeu4u
Starting Member

United Kingdom
2 Posts

Posted - 04/08/2010 :  09:10:38  Show Profile  Reply with Quote
Hi Guys

I have 3 tables, table 1 holds details about a message, table 2 holds the audiences the message is for, and table 3 holds details of all users who have confirmed they have read a message,

What im trying to do is display, all messages for a particular user, where the publish date is greater than or equal to todays date, and where the messageid, username, does not appear in table 3, which shows they havent read the message, (Basically showing users specific messages applicable to them which they have not yet read) its for a vb.net application im building, Ive put the query below and whilst its almost write, its not doing what i want, which is bascially the part about showing the messages they havent yet read, at the moment its returning results for other users, also, i only want it to show results for the particular user, who has not yet read the particular message.

SELECT commsPlatformChangeMsg.changeMsgID, commsPlatformChangeMsg.changeMsgTitle, commsPlatformChangeMsg_MuID.MU_ID,
commsPlatformChangeMsg_MuID.site, commsPlatformChangeMsg.changeMsgActive, commsPlatformChangeMsg.changeMsgPublishDate,
commsPlatformChangeMsg_Read.username, commsPlatformChangeMsg.changeMsgPageURL,
commsPlatformChangeMsg_Read.changeMsgID AS read_MessageID
FROM commsPlatformChangeMsg INNER JOIN
commsPlatformChangeMsg_MuID ON commsPlatformChangeMsg.changeMsgID = commsPlatformChangeMsg_MuID.changeMsgID CROSS JOIN
commsPlatformChangeMsg_Read
GROUP BY commsPlatformChangeMsg.changeMsgID, commsPlatformChangeMsg.changeMsgTitle, commsPlatformChangeMsg_MuID.MU_ID,
commsPlatformChangeMsg_MuID.site, commsPlatformChangeMsg.changeMsgActive, commsPlatformChangeMsg.changeMsgPublishDate,
commsPlatformChangeMsg_Read.username, commsPlatformChangeMsg.changeMsgPageURL,
commsPlatformChangeMsg_Read.changeMsgID
HAVING (commsPlatformChangeMsg_MuID.MU_ID = @MU_ID) AND (commsPlatformChangeMsg_MuID.site = @site) AND
(commsPlatformChangeMsg.changeMsgActive > 0) AND (CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112)) >= CONVERT(datetime,
CONVERT(varchar(8), commsPlatformChangeMsg.changeMsgPublishDate, 112))) AND
(NOT (commsPlatformChangeMsg_Read.username IN (@username))) AND
(NOT (commsPlatformChangeMsg.changeMsgID IN (commsPlatformChangeMsg_Read.changeMsgID)))

Thanks in advance for your help and support

Dan

No one ever said it was going to be easy, then again, nothing worth doing ever is.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/08/2010 :  11:05:28  Show Profile  Reply with Quote
something like

SELECT required columns....
FROM Table1 t1
INNER JOIN Table2 t2
ON t.messageid=t2.messageid
AND t2.username = @yourpassedusername
LEFT JOIN Table3 t3
ON t3.messageid = t2.messageid
AND  t3.username = t3.username
WHERE t3.messageid IS NULL
AND t1.publishdate > = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 04/08/2010 11:06:05
Go to Top of Page

teddymeu4u
Starting Member

United Kingdom
2 Posts

Posted - 04/09/2010 :  04:09:03  Show Profile  Reply with Quote
Hi visakh16 thanks for the reply, however im looking for items where the user hasnt already read them, maybe if i give you the table structures youll get a better idea.

Table 1
MessageID (int PK)
title (varchar200)
Created (smalldatetime)
PageURL

Table2
MessageID (int)
MU_ID (int)
Site (varchar)

Table 3
messageID (int)
username (varchar11)
readit (smallint)

Ok, so i want to know, all articles for the username, where the created date is greater than or equal to todays date, and the username and message id, doesnt appear in table 3, i am passing in th username, site, and MU_ID, in the query string as parameters.

Thanks

No one ever said it was going to be easy, then again, nothing worth doing ever is.
Go to Top of Page

RobertKaucher
Posting Yak Master

USA
169 Posts

Posted - 04/09/2010 :  10:00:05  Show Profile  Visit RobertKaucher's Homepage  Reply with Quote
How can you determine if the user has read the message and what is the language for the front end? Does readit = some value? It would be helpful if you posted some sample data.



===
http://www.ElementalSQL.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/11/2010 :  03:21:46  Show Profile  Reply with Quote
quote:
Originally posted by teddymeu4u

Hi visakh16 thanks for the reply, however im looking for items where the user hasnt already read them, maybe if i give you the table structures youll get a better idea.

Table 1
MessageID (int PK)
title (varchar200)
Created (smalldatetime)
PageURL

Table2
MessageID (int)
MU_ID (int)
Site (varchar)

Table 3
messageID (int)
username (varchar11)
readit (smallint)

Ok, so i want to know, all articles for the username, where the created date is greater than or equal to todays date, and the username and message id, doesnt appear in table 3, i am passing in th username, site, and MU_ID, in the query string as parameters.

Thanks

No one ever said it was going to be easy, then again, nothing worth doing ever is.


i think thats exactly what i've given you.

SELECT required columns....
FROM Table1 t1
INNER JOIN Table2 t2
ON t.messageid=t2.messageid
AND t2.username = @yourpassedusername
LEFT JOIN Table3 t3
ON t3.messageid = t2.messageid
AND  t3.username = t3.username
WHERE t3.messageid IS NULL
AND t1.publishdate > = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND  t1.username = @User
AND site = @site 
and MU_ID = @MUID


can you explain why you think its not working may be with some data example from tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000