Table joins and I suck at SQL

Question

question

Carlos asked Apr 7, '10

Table joins and I suck at SQL

Have three tables:

GroupTable Table
GroupId | GroupName

GroupCompanyRelation Table
GroupId | CompanyId

UserCompanyRelation Table
CompanyId | UserId

And this query:

SELECT grp.* FROM GroupTable grp
WHERE grp.GroupId IN
(SELECT gcr.GroupId FROM GroupCompanyRelation gcr WHERE gcr.CompanyId IN
(SELECT ucr.CompanyId FROM UserCompanyRelation ucr WHERE ucr.UserId = 1621733))

How can I make this same query using using joins? Seems like I should be able to get the same results using a join or 2 but then again I am a SQL dummy.

t-sql joins

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-07T19:33:25Z

Squirrel 1 answered Apr 7, '10

select *            
from   GroupTable grp            
       inner join GroupCompanyRelation gcr ON grp.GroupId = gcr.GroupId             
       inner join UserCompanyRelation ucr  ON gcr.CompanyId =ucr.CompanyId             
where  ucr.UserId = 1621733

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-07T22:05:13Z

Squirrel 1 answered Apr 7, '10

if you are looking only for listing from GroupTable, use EXISTS()

select *            
from   GroupTable grp            
where  exists            
       (            
           select *            
           from   GroupCompanyRelation gcr             
                  inner join UserCompanyRelation ucr  ON gcr.CompanyId =ucr.CompanyId             
           where  ucr.UserId = 1621733            
           and    gcr.GroupId = grp.GroupId             
       )

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question