Inner join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inner join?

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2002-11-22 : 09:57:49

Yep still found a "Good" question.
I have:
tbl_user
tbl_link_user_group

No I want to view all users from the selected group.
SO I have a group_ID.

SELECT user_ID FROM tbl_link_user_group WHERE group_ID = '"& theID &"'"

No problem there but know I want to sort all the listed names by Firstname.

So thats the problem. I allready tried:
SELECT tbl_link_user_group.user_ID, tbl_user.firstname FROM tbl_link_user_group, tbl_user WHERE tbl_link_user_group.directory_ID = '"& theID &"' ORDER by tbl_user.firstname

But that was not a list I wanted. So thanxx for the help!

Cya
Bjorn

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-22 : 10:02:40

quote:

Yep still found a "Good" question.
I have:
tbl_user
tbl_link_user_group

No I want to view all users from the selected group.
SO I have a group_ID.

SELECT user_ID FROM tbl_link_user_group WHERE group_ID = '"& theID &"'"

No problem there but know I want to sort all the listed names by Firstname.

So thats the problem. I allready tried:
SELECT tbl_link_user_group.user_ID, tbl_user.firstname FROM tbl_link_user_group, tbl_user WHERE tbl_link_user_group.directory_ID = '"& theID &"' ORDER by tbl_user.firstname

But that was not a list I wanted. So thanxx for the help!

Cya
Bjorn

I'm a bit confused. You seem to have changed which ID you are checking on between the two queries. If you want to list all the users in a group and group_id is present in tbl_user as a foreign key then

SELECT firstname from tbl_user where group_id = theid
order by firstname

would do what you need.

-------
Moo.

neutcomp
Posting Yak Master

111 Posts

Posted - 2002-11-22 : 10:12:47

Nop wont work.
Little explanation. I have users with right. But to give all users the same rights I made a group. And yep that group has certain rights.

So know I want to look up all the users in the selected group. Because I have a moderator group and a administrator group.

So tbl_user is the user table with the firstname and user_id
tbl_link_group_user is the table with the user_id and group_id

The only thing I know when I enter the page is the group_id. So I can allready build the page of names with this query.

SELECT user_ID FROM tbl_link_group_user WHERE directory_ID = '"& theID &"'"

And then a nother query:
SELECT firstname FROM tbl_user WHERE user_ID = '"& user_ID &"'

But then I cannot order by name because the first query is the one which determines the order.

Thanxx
Bjorn

Cya
Bjorn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 10:13:40

I think the issue is that the groups a user belongs to is NOT in the user table, but in the link table. That is, a user can belong to more than one group.

So, you would have



SELECT
  user.lastname,
  user.firstname,
  group.name
FROM
  tbl_user user
INNER JOIN
  tbl_link_user_group grouplink
ON
  user.user_Id = grouplink.user_Id
INNER JOIN
  tbl_groups group
ON
  group.group_ID = groulink.group_ID
WHERE
  group.group_ID = @GroupIDToShow
ORDER BY
  user.lastname

That is assuming you have a seperate groups table with the group names and such.

- Jeff

neutcomp
Posting Yak Master

111 Posts

Posted - 2002-11-22 : 10:35:39

Almost there, the only thing know happend is that I get from one group_ID:
NAME -- ID
bjorn -- 12
bjorn -- 12
bjorn -- 12
bjorn -- 12
bjorn -- 12
bjorn -- 12
bjorn -- 12
marijn -- 23
marijn -- 23
marijn -- 23
marijn -- 23
marijn -- 23
marijn -- 23
marijn -- 23

And how I can see it know the times one user_id is displayed is the amount of user that are in that group.

Hope you understand the problem.

Cya
Bjorn

neutcomp
Posting Yak Master

111 Posts

Posted - 2002-11-22 : 10:38:44

I found the problem!

There was one innerjoin to mutch.

Thanxx

Cya
Bjorn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 10:40:16

So a user can be in a group more than once? hmmm, sounds a little odd.

Just use GROUP BY in your results or use SELECT DISTINCT.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources