SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Only return one from 1 to many join

    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
    monkey - the rest is history

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It sounds like you need an intermediary table matching the primary key for roles and the primary key for people.

  3. #3
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by eruna View Post
    It sounds like you need an intermediary table matching the primary key for roles and the primary key for people.
    Explain further! The Key of the person is linked to a foreign key in the roles.
    monkey - the rest is history

  4. #4
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,056
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    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. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by boxhead View Post
    Explain further! The Key of the person is linked to a foreign key in the roles.
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by boxhead View Post
    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.
    please script out all the tables (there should be three of them) so that we can see the CREATE TABLE statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •