Join vs IN vs anything else - 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)

Join vs IN vs anything else

Author

Topic

hendribt
Starting Member

4 Posts

Posted - 2007-05-21 : 15:57:36

I have a view that contains the following columns... AccountID, UserID, and RoleCode. (There are more columns but those are the important ones.) I need to select the Account ID where there is a record in the view that has both a role code of 1 and 3. The account ID shows up multiple times in this view, just with different role codes associated to it.

Which would be a more efficient way of doing this...

SELECT v1.AccountID, v1.UserID, OT.OrgName FROM View1 v1
JOIN OrgTable OT
ON OT.AccountID = v1.AccountID
WHERE RoleCode = 1
AND AccountID IN (SELECT AccountID FROM View1 WHERE RoleCode = 3)

or

Select v1.AccountID, v1.UserID, OT.OrgName FROM View1 v1
JOIN OrgTable OT
ON OT.AccountID = v1.AccountID
JOIN View1 v
ON v.AccountID = OT.AccountID
WHERE v.RoleCode = 3 and v1.RoleCode = 1

or

(anything better than the above two examples)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 16:13:37

neither.

select accountID
from View1
where roleCode in (1,3)
group by accountID
having count(*) = 2

If your view might return more than 1 row per AccountID/RoleCode combination, then change the last part to:

having count(distinct RoleID) = 2

- Jeff
http://weblogs.sqlteam.com/JeffS

hendribt
Starting Member

4 Posts

Posted - 2007-05-21 : 16:55:25

Thank you for the reply! This isn't quite working because the query is a bit more complicated that I originally posted. I'll just post the whole query as I have it. I am currently selecting data for accounts that have a role code of 1. I need to get accounts that have an entry for both role codes of 1 and 2

SELECT
DA3.AccountID,
DA3.PSUserId,
DA3.RoleCode,
DA3.RoleDesc,
DA3.PrimaryFlag,
DA3.EffectiveDate,
DA3.ExpirationDate,
U1.FirstName,
U1.LastName,
U1.JobTitle,
U2.FirstName as ManagerFirstName,
U2.LastName as ManagerLastName,
U1.ManagerName,
U1.SupervisorID,
B2BC.Segment,
B2BC.SegmentCD,
B2BC.SubSegment,
B2BC.SubSegmentCD,
B2BC.Geo,
B2BC.GeoCD

FROM DA_vw3 DA3

INNER JOIN Users U1
on DA3.PSUserId = U1.UserToken

INNER JOIN Users U2
on U1.SupervisorID = U2.UserToken

JOIN B2BC_vw3 B2BC
ON B2BC.AccountID = DA3.AccountID

WHERE DA3.RoleCode = 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 18:23:47

join your bigger query to the query that returns only the AccountID's that you need.

select *
from
( you sql here) a
inner join
( my sql here) b on a.accountID = b.accountID

It is important to break complicated selects down into steps. see:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources