Many to many query

[caser85]

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?

[r937]

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

[Snowbird122]

I'm no sql expert and I would like to learn more about joins. I would answer the question with this query:

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?

[r937]

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

[DaveMaxwell]

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.

[redemption]

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.

[r937]

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

[redemption]

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.

[r937]

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?