a tricky view - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

a tricky view

Author

Topic

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-27 : 16:56:50

Allllrighty.. if anyone can assist with this one, I'd apppppreciate it. :)

Overview (this if for an instant messenger that I didn't write):

I have 3 tables - TableName(Columns):
Staff (StaffID, ChatEnabled)
ChatUsers (StaffID, ChatID, NickName)
ContactList (ChatID_User1, ChatID_User2)

I turned the ContactList table into a View instead of an actual table. The ContactList consist of every single ChatID in the ChatUsers table paired with the ChatID of every other user in the table. The code to create my view is:


create view ContactList
as
select cu1.ChatID as ChatID_User1, cu2.ChatID as ChatID_User2
from ChatUsers as cu1
cross join ChatUsers as cu2
where cu1.ChatID <>  cu2.ChatID

(thanks to dinakar for the help with that cross join.. ugh)

Now comes the tricky part... instead of automatically grabbing every single user from the ChatUsers table, I need to only grab users that have ChatEnabled = True in the Staff table. I KNOW this is really dumb - but again - I didn't write the program. I'm doing what I can with what I've got.

For instance, if StaffID #1 has ChatEnabled set to false, he should appear as neither Chat_User1 nor Chat_User2 in the ContactList view.

Clear as mud? Any suggestions? I know I have to join ChatUsers to Staff based on StaffID in my view, but I don't know how or where to properly do that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 17:09:19

[code]CREATE VIEW ContactList
AS

SELECT cu1.StaffID AS ChatID_User1,
cu2.StaffID AS ChatID_User2
FROM ChatUsers AS cu1
INNER JOIN Staff AS s1 ON s1.StaffID = cu1.StaffID
INNER JOIN ChatUsers AS cu2 ON cu2.ChatID = cu1.ChatID
INNER JOIN Staff AS s2 ON s2.StaffID = cu2.StaffID
WHERE cu2.StaffID <> cu1.StaffID
AND s1.ChatEnabled = 1
AND s2.ChatEnabled = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

Jenda
Starting Member

29 Posts

Posted - 2007-08-27 : 18:58:08

quote:
Originally posted by ShooterJ07
ContactList (ChatID_User1, ChatID_User2)

I turned the ContactList table into a View instead of an actual table. The ContactList consist of every single ChatID in the ChatUsers table paired with the ChatID of every other user in the table.

May I ask why? What's the purpose of such a view then? Originaly you were able to specify that UserA has in his/her contact list the UserB, UserD and UserM. With this view everyone will have everyone else in the contact list!?! Doesn't sound too handy to me.

You should not belittle the original author of the messenger, maybe he had a reason to design things the way he did and you just did not understand it yet.

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-28 : 09:35:16

quote:
Originally posted by Jenda

May I ask why? What's the purpose of such a view then? Originaly you were able to specify that UserA has in his/her contact list the UserB, UserD and UserM. With this view everyone will have everyone else in the contact list!?! Doesn't sound too handy to me.

You should not belittle the original author of the messenger, maybe he had a reason to design things the way he did and you just did not understand it yet.

The software is pretty well written - it's just that I am not going to use it in the same manner it was intended. The program was written to function like any other messenger - to allow users to control their contact list by adding/removing friends. I need every registered user to show up in the contact list (there won't be hundreds of registered users or anything), and they will only be allowed to communicate with users who are registered on their own website.

Just as an example, let's say I was deploying this in a small school. The teachers would see a contact list of all other teachers when they log on, preventing them from having to search for contacts. This was a specific request of the cusotmers we are deploying to - not just something we randomly thought up. The end users are not necessarily the most tech-savy people in the world, and we simply don't want them to have to search for their contacts.

Using a view instead of a table keeps me from having to execute an UPDATE statement everytime a new user is registered or deleted or disables their messenger. If I used a normal table, I would have to not only delete their contact list, but also delete them from the contact list of every other user.

I didn't mean to belittle or criticize the developer of the software - I have even been getting advice from them about how to best implement it the way I want. It's just because I'm not following their classic model that I'm having to do this funky stuff that is less than desirable, but still functional.

ShooterJ07
Starting Member

17 Posts

Posted - 2007-08-28 : 10:10:39

Thanks Peso.. I just had to use a crossjoin instead of an inner on one of those lines to get the desired results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 10:13:25

Ok, if you say so.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources