SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict caser85's Avatar
    Join Date
    May 2002
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many to many query

    I need some help setting up a query for a web site. I have three tables:

    tblUser //contains users
    tblRole //contains roles i.e. administrator, faculty
    tblUserRole //linking table for many-to-many relationship

    tblUserRole contains two fields, user_id and role. These are linked from the other fields. What I want to do is query this table and display the roles for each user in a listbox. Does anyone know how I can do this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select U.user_id
    	 , U.user_name
    	 , R.role
    	 , R.role_name
      from tblUser U
    inner
      join tblUserRole UR
    	on U.user_id = UR.user_id
    inner
      join tblRole R
    	on UR.role_id = R.roleid
    order
    	by U.user_name
    	 , R.role_id
    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm no sql expert and I would like to learn more about joins. I would answer the question with this query:
    Code:
    select tblUser.username, tblRole.role from tblUser, tblRow where tblUser.user_id = tblUserRold.user_id and tblRole.role_id = tblUserRole.role_id
    1. Is this also correct?
    2. If so, what advantages does Rudy's query have over mine?
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    snowbird122, your query is missing one of the tables in the FROM clause

    other than that, the difference is that yours uses table list syntax whereas mine uses JOIN syntax

    the advantage of JOIN syntax is that it's easier to write, easier to read, and easier to understand

    rudy

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937
    other than that, the difference is that yours uses table list syntax whereas mine uses JOIN syntax

    the advantage of JOIN syntax is that it's easier to write, easier to read, and easier to understand

    rudy
    maybe for you, but I personally find the list syntax much easier to write and read. Guess it's a personal preference.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used to think so too because it was all I knew. Then I read a short article on the JOIN syntax and it was easy to pick up.

    It makes perfect sense to separate your join condition from the other conditions. You'll appreciate it when you start having a long WHERE clause and try to make sense of it.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    outer joins are substantially easier to write, too

    i could never figure out which side of the equal sign that stupid asterisk -- or is it a plus sign in parentheses? -- is supposed to go

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i could never figure out which side of the equal sign that stupid asterisk -- or is it a plus sign in parentheses? -- is supposed to go
    It's a (+) in Oracle I think.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yeah, so in sql server, it's *= ... or is it =* ? i can never remember

    and which side do you put these things on, the left side for a LEFT OUTER or the right? because in a LEFT OUTER, it's the right side rows which may be missing

    see what i mean about LEFT OUTER JOIN being easier to understand?



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
  •