Skype like query in a lookup table

Question

5.00/5 (1 vote)

See more:

Hi all,
currently I am struggling with a query that I try to create.
It is based on the following table structure:

SQL

CREATE TABLE zzzTestQuery1(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[mId] [int] NOT NULL,
	[uId] [int] NOT NULL)

mId is a messageId while uId is the userId
This table functiones as a lookup table in order to assign n users to a message.

I now try to get all messages that are assigned to a specific user group.
Means that I need to get all messages that are assigned to (exmpl.) user 1, 3, 5 and only axactly these three users. No messages that have an additional user assigned and no messages that have less than the specified users assigned.
(I hope that this explanation is clear enough, unfortunately I am not a native speaker, so ask if something is unclear).

The userId's I have to look up are dynamic.

I can produce the list of messages using C#, but I need to grab a lot of records in order to process them, which feels somehow uncomfortable.

How could I approach this requirement. I thought about flattening the hirarchy so that a message gets a new (maybe computed) column that contains a comma separated list of userId's (sorted) that are assigned to a message...

Any help is kindly appreciated,
have a great day
Andy

Posted 12-Mar-12 22:14pm

hoernchenmeister

Add a Solution

Comments

Herman<T>.Instance 13-Mar-12 4:49am

do you select the users in a c# program?

hoernchenmeister 13-Mar-12 4:56am

Yes, that's right.
Someone can select 1 to n users and then the application should display all messages that have been sent to the selected users (exactly the selected ones).

hoernchenmeister 13-Mar-12 4:57am

I should mention that I get the data using Entity Framework, but I could create stored prcedures/views as well.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Answer 1 · 2012-03-12T23:03:00

Solution 1

What I Do:
I have a table with a field for an unique ID, a varchar field(50) for a GUID value and a field for the user id
I have a checkboxlist in which I can select users. When clicking a submit button I do:
- create a New Guid()
- scan for all checked checkboxes.
Each checkbox and the guid are stored in table.
After that I call an stored procedure that has the Guid as input parameter
In that stored procedure I can

C#

select m.Message
from tblMessage as m
inner join zzzTestQuery1 as z on z.mId = m.mid
where z.uid in (select uid from myTable where guidField = @guid)

By using the guid you have one unique reference to the messages form the selected users.

Have fun

Posted 12-Mar-12 23:03pm

Herman<T>.Instance

v2

Comments

hoernchenmeister 13-Mar-12 5:33am

Interesting idea digimanus,
what you basically mean is to store the group instead of having a lookup table for users, did I get this right?

simplified:
Table Messages(id, text, timestamp)
Table MessageGroupLookup(id, messageId, GroupGuid)
-> where I would use the userIds to create a unique sequence by transforming the ids into a comma separated, ordered (by id) string serving as the guid.

like: '1,3,6,8'

It would simplify my setup and lets me easily get the messages.
Only drawback would be that it makes it harder to lookup messages that have been sent to a specific user (if there would be a need for it in the future).

What do you think? Did I get your idea right?

Thanks for your input digimanus, it is very much appreciated.
cheers
Andy

Herman<T>.Instance 13-Mar-12 5:36am

well you set the users in the table and by using the in (select) you can join inside your database because all the information is available. So you do not make the '1,3,5' string. You reference by the selected Uid's based on the generated guid

Herman<T>.Instance 13-Mar-12 5:37am

remember to delete the data after retrieving your resultset

DominicZA · Answer 2 · 2012-03-12T23:12:00

I assume what you mean is get the users that are in the same conversation? Since you are using the Entity Framework you should be able to do it like this:

C#

var usersInChat = (from m in myDataContext.Messages
                                 group m.Users in ChatUsers
                                 where m.mID = messageID
                                 select ChatUsers.Key);

Assuming that you have set up you database correctly this should work.

P.S: Im not by my PC now so I cant double check, but the point I am trying to get across is use grouping :P

http://msdn.microsoft.com/en-us/library/bb896250.aspx[^]

Skype like query in a lookup table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Skype like query in a lookup table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us