Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
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
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.

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
 
Share this answer
 
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
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[^]
 
Share this answer
 
Comments
hoernchenmeister 13-Mar-12 5:21am    
Actually I already have the users I am intersted in and try to lookup the messages.

Assume I have userids: 1,3,5
Now I need all messages that have been assigned to user 1 and 3 and 5. Not more that the specified users and not less.
I also thought about grouping, but I just coudn't imagine how that should work ;)

Thanks for taking the time DominicZA, it is very much appreciated.
Cheers
Andy
Herman<T>.Instance 13-Mar-12 5:30am    
It shows the power of Linq again!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900