How to use in my application a SELECT statement that must return more than one record to show multiple values for a certain field (m:m relation)

I don't know if you got what I mean, but I'll try to explain it with an example.

Users Table

UsedId    UserName
--------  ----------
1         Mike
2         Raul

HasPrivileges Table

UsedId       PrivilegeId      
--------     --------------
1            1
1            2
1            3
2            2
2            3

Privileges Table

PrivilegeId      Privilege
-------------    ------------
1                Create
2                Edit
3                Delete

now this is two tables users and privileges that has a many-to-many relation between them, so when I select all the users associated with the privileges they have, I get the in this examples 3 records or rows in result for Mike each one contains a privilege he has.

Now I need in my application to display a list of all the users with their privileges but INDEED i don't want my users to see a user three times to show all of his privileges or anything else instead I want it to display

User Id : 1
Name : Mike
Privileges : Create, Edit, Delete

or something close to this! ANY IDEAS GUYS !开发者_如何学Python??

Ikashef, as Tomalak said, suppressing the repeating name from each of the name/permissions rows is a "presentation-layer" issue, i.e. how you display data to your users.

What you want to do is look at ADO.NET DataTable to get these rows back:

       Joe    1
       Joe    7
       Joe    8
       Tom    3
       Tom    7
       Tom    8

The DataTable has a Rows property, which contains a collection of rows. You can iterate over (i.e. visit in turn) each DataRow in the Rows collection. So read up on ADO.NET DataTable object and on collections classes and on the "for each" syntax.

Ok there are 3 points I can identify with this current problem (I've got a similar thing in my own project).

Bitwise

You can virtually eliminate one of your tables by using a bitfield as opposed to a join table. For example, rather than storing the HasPrivilages along with a privileges table.... You can do this:

UsedId       PrivilegeId      
--------     --------------
1            1
1            2
1            3
2            2
2            3

Could equate to:

UsedId       PrivilegeId      
--------     --------------
1            7 (equivalent of Create, Edit and Delete)
2            6 (equivalent of Create and Delete)

This is because Create = 1, Edit = 2 and Delete = 4. Combined, they form a single integer number. This can be differentiated using Bitwise operations, like & and | to produce combinations of permissions.

You'd declare your set of permissions, with the Flags attribute like

[Flags()]
public enum Permissions {
    Create = 1,
    Edit = 2,
    Delete = 4
}

When you read the value back, the enum will calculate the actual permissions for you, and you can work it out in your application by doing an operation such as:

bool canEdit = ((myUser.Permissions & Permissions.Edit) == Permissions.Edit);

If you have the appropriate Permissions enum, doing a .ToString() on that given instance will actually give you the permissions data you require. It is however preferable to give the enum an custom attribute so you can give each value a better name, or even make it language independent from a resource.

Formatting for presentation

You can of course stick with what you've got, and use the example Tim has given. Iterate over the rows and essentially precalculate the text.

Do it in SQL

Sometimes it's just easier to get SQL to do the work. I've done this a lot. If you're just getting DataTables back as opposed to reading them manually or using LINQ, this is a quick fix. If you're using SQL Server 2005 or above, you can use code similar to:

SELECT  u.UserId, 
        u.UserName AS [Name],
        (
             SELECT     DISTINCT Privilege + ', '
             FROM       Privileges p2
                        INNER JOIN HasPrivileges j ON j.PrivilegeId = p2.PrivilegeId
             WHERE      j.UserId = u.UserId
             FOR XML PATH ('')
        ) AS [Privileges]
FROM    Users u
        INNER JOIN HasPrivileges h ON h.UserId = u.UserId
GROUP BY u.UserId, UserName

This outputs:

UserId  Name   Privileges
------- -----  -----------
1       Mike   Create, Delete, Edit, 
2       Paul   Delete, Edit, 

This still isn't perfect. You'd have to load this into a temp table and strip the final "," char off the end of each Privileges column, or do it within your C# code.

Anyway just thought I'd offer some alternatives, Tom.

You have ASP.NET and C# in your tags. Considering that what you intend to do is a presentational issue, do it in the presentation layer (i.e. with C#) and not in the data layer (i.e. with SQL). That's a lot easier, too.

For example, like shown here: Use LINQ to concatenate multiple rows into single row (CSV property)