Finding announcements based on user & customer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding announcements based on user & customer

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 06:58:07

This is a tough one to describe because my TSQL skills aren't too clever, but here goes...!

I have 2 tables, tbl_Users and tbl_Announcements.

The idea is that a user logs into my system, and then can see any announcements posted since they last read the announcements board.

I need to find all the announcements based on the following search criteria:

1.) The announcement's customerID is the same as the user's customerID
OR
The announcement's customerID is NULL (used for announcements targetted at all customers)

2.) The announcement's dateAdded column is greater than or equal to the user's lastReadAnnouncements date (so only new announcements are found).

My SQL statement is as follows but its just not pulling out any results. Can anyone see any obvious mistakes? I can post any additional information if it will help!

SELECT
tbl_Announcements.dateAdded,
tbl_Announcements.announcementTitle,
tbl_Announcements.announcementText
FROM
tbl_Announcements LEFT OUTER JOIN
tbl_Users ON tbl_Announcements.dateAdded >= tbl_Users.lastReadAnnouncements AND tbl_Announcements.customerID = tbl_Users.customerID
WHERE
(tbl_Users.userID = 3) AND (tbl_Announcements.customerID = 2 OR
tbl_Announcements.customerID IS NULL)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 07:07:23

Try this


SELECT
tbl_Announcements.dateAdded,
tbl_Announcements.announcementTitle,
tbl_Announcements.announcementText
FROM
tbl_Announcements LEFT OUTER JOIN
tbl_Users t1 ON tbl_Announcements.customerID = t2.customerID
WHERE
(tbl_Users.userID = 3) AND (tbl_Announcements.customerID = 2 OR
tbl_Announcements.customerID IS NULL)
And tbl_Announcements.dateAdded >= (Select Max(lastReadAnnouncements) From tbl_Users t2 Where t1.CustomerID = t2.CustomerID)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 07:15:06

Hi chiragkhabaria
Thanks for the quick post. Unfortunately it throws an error: The column prefix 't2' does not match with a table name or alias name used within the query

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 07:18:40

sorry try this


SELECT
tbl_Announcements.dateAdded,
tbl_Announcements.announcementTitle,
tbl_Announcements.announcementText
FROM
tbl_Announcements LEFT OUTER JOIN
tbl_Users t1 ON tbl_Announcements.customerID = t1.customerID
WHERE
(tbl_Users.userID = 3) AND (tbl_Announcements.customerID = 2 OR
tbl_Announcements.customerID IS NULL)
And tbl_Announcements.dateAdded >= (Select Max(lastReadAnnouncements) From tbl_Users t2 Where t1.CustomerID = t2.CustomerID)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 07:28:25

Hi again. This suggestion is suffering the same problem as my own in that it doesn't return any rows of data. Could I provide any more info that would be of help?? I appreciate your help.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 07:30:32

I guess you require to
post same sample data from both the tables and their data types

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 08:01:28

I excluded the (tbl_Users.userID = 3) AND (tbl_Announcements.customerID = 2) from the query as I don't know what it is for. Anyway you can you add your other conditions if the following is what you want.

SELECT 	a.dateAdded,
	a.announcementTitle,
	a.announcementText
FROM	tbl_Users u INNER JOIN tbl_Announcements a
ON 	a.dateAdded 		>= u.lastReadAnnouncements 
AND 	(
		a.customerID 	= u.customerID
	or	a.customerID	is null
	)

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 08:03:58

Sorry chiragkhabaria I'm going to have to back up a step. I've just set the lastReadAnnouncements date field in tbl_Users (some were NULL) and your suggestion now pulls out 1 row. The problem is that it does not find the row where tbl_Announcements.customerID IS NULL

The sample data is included for reference anyway...

tbl_Users
---------
userID int
customerID int
lastReadAnnouncements datetime

tbl_Announcements
-----------------
customerID int
dateAdded datetime
announcementTitle nvarchar 50
announcementText nvarchar 500

Data Samples:

tbl_Users
---------
userID,CustomerID,lastReadAnnouncements
3,2,01/01/2005
4,2,01/01/2005

tbl_Announcements
-----------------
customerID,dateAdded,announcementTitle,announcementText
NULL,22/05/2006 10:28:27,Here is the first announcement,Sample text Sample text Sample text Sample text Sample text
2,22/05/2006 10:30:24,CompanyX-specific Announcement,Sample text Sample text Sample text Sample text Sample text
1,22/05/2006 11:08:11,CompanyY-specific Announcement,Sample text Sample text Sample text Sample text Sample text

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 08:35:58

Hi khtan
>> I excluded the (tbl_Users.userID = 3) AND (tbl_Announcements.customerID = 2) from the query as I don't know what it is for.

userID = 3 narrows the search down to only show announcements that that particular user hasn't viewed. I was trying to get the lastReadAnnouncements value from the row where userID = 3.
customerID basically ensures that the person can only see announcements on their own company's portal.

I ran your suggestion and although it pulled out the correct rows, it pulled each one out twice so I don't know what's happening...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-22 : 08:40:29

Check out this


Declare @tbl_Users Table 
(
userID int,
customerID int,
lastReadAnnouncements datetime
)

Declare @tbl_Announcements Table 
(
customerID int,
dateAdded datetime,
announcementTitle nvarchar (50),
announcementText nvarchar (500)
)

Insert @tbl_Users 
Select 3,2,'01/01/2005' Union All 
Select 4,2,'01/01/2005' 

Insert @tbl_Announcements 
Select NULL,'2005/05/22 10:28:27 ','Here is the first announcement','Sample text Sample text Sample text Sample text Sample text' Union All 
Select 2,'2005/05/22 10:30:24','CompanyX-specific Announcement','Sample text Sample text Sample text Sample text Sample text' Union All 
Select 1,'2005/05/22 11:08:11','CompanyY-specific Announcement','Sample text Sample text Sample text Sample text Sample text'



Select A.CustomerID,A.dateAdded,A.announcementTitle,	A.announcementText 
From @tbl_Announcements A  Left Outer Join 
@tbl_Users B On A.CustomerID = B.CustomerID 
Where (A.dateAdded >= B.lastReadAnnouncements Or A.CustomerID is null )

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 09:10:04

try this

SELECT 	distinct
        a.dateAdded,
	a.announcementTitle,
	a.announcementText
FROM	tbl_Users u INNER JOIN tbl_Announcements a
ON 	a.dateAdded 		>= u.lastReadAnnouncements 
AND 	(
		a.customerID 	= u.customerID
	or	a.customerID	is null
	)

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 10:41:40

quote:

Originally posted by chiragkhabaria

Check out this


Select A.CustomerID,A.dateAdded,A.announcementTitle,	A.announcementText 
From @tbl_Announcements A  Left Outer Join 
@tbl_Users B On A.CustomerID = B.CustomerID 
Where (A.dateAdded >= B.lastReadAnnouncements Or A.CustomerID is null )

Thanks, but sadly this pulls out the row twice where tbl_Announcements.customerID = 2

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-05-22 : 10:42:45

quote:

Originally posted by khtan

try this

SELECT 	distinct
        a.dateAdded,
	a.announcementTitle,
	a.announcementText
FROM	tbl_Users u INNER JOIN tbl_Announcements a
ON 	a.dateAdded 		>= u.lastReadAnnouncements 
AND 	(
		a.customerID 	= u.customerID
	or	a.customerID	is null
	)

This has sorted it. Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources