Only return one from 1 to many join

Databases
1

I have a table of people and a table of roles. A person can have several roles. I have a query which joins the two. How do I only return one of the joins per person - I don’t care which join it returns. I needs all columns from both tables.

This is in MSSQL

cheers

monkey

2

It sounds like you need an intermediary table matching the primary key for roles and the primary key for people.

3

Explain further! The Key of the person is linked to a foreign key in the roles.

4

If you are linking from one person to many rules, but don’t care which is returned then you can group on the user’s id (the primary key of the first table) and that’ll return a single line for users then.

I’m not sure about MS SQL but I know mySQL has a ‘group_concat’ function which would allow you to make, for example, a comma separated list of roles.

5

So 1 person can have many roles, but 1 role can be linked to only 1 person? There are no roles that can be of many persons?

Of course, it’s hard to tell without knowing what a ‘role’ is, and what data the ‘roles’ table contains.

6

please script out all the tables (there should be three of them) so that we can see the CREATE TABLE statements

7

This is formatting that would be done in the application language after the result has been returned from the database with all the duplicates in raw form.