Join 2 Tables via 3rd Table 1

[cwsstins]

I am trying to get user and security group data from two different tables (users & secgroup). However there is no join between the two tables. There is a third table (secbygrp) that allows a one-to-many join from each of the first two tables. For instance:

users.id
secgroup.groupid
secbygrp.id
secbygrp.groupid

users.id >> secbygrp.id
secgroup.groupid >> secbygrp.groupid

How can I go about selecting records from users and their corresponding secgroup records using the secbygrp table as the join?

This is what I have right now and I know why it's wrong, but I just can't wrap my head around how to join the two tables through the third table.

SELECT u.code, u.descrip, sg.code, sg.descrip
FROM users u, securitygroup sg
WHERE u.id in (SELECT id from secbygrp sbg)
and sg.groupid in (SELECT groupid from secbygrp sbg)

 

[gmmastros]

Try this:

Select u.code, u.descrip, sg.code, sg.descrip
from   users U
       Inner Join secbygrp
         On u.id = secbygrp.id
       Inner Join secgroup
         On secgroup.groupid = secbygrp.groupid

This is not tested, but should work ok for you. If this does work, and you would like me to explain it, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cwsstins]

That works! Thanks a lot. I see how it works.