Click here to Skip to main content
15,794,593 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello I created a query which gives me 29 records exactly

 select u.Name,u.UserDescription, u.UserEmail, g.GroupDescription
 from [GroupMembers] gm
 inner join [Users] u
 on gm.GroupMemberObjectID = u.UserID
 inner join[Groups] g
 on g.GroupID = gm.GroupID
 where gm.GroupID = 43
 and gm.GroupMemberObjectType = 205
order by u.UserDescription

What I would like to do is join my Apps table to the above query but with only the condition where gm.GroupMemberObjectType is 201.

select a.AppDescription
  from [GroupMembers] gm
  inner join [Apps] a
  on a.AppID = gm.GroupMemberObjectID
  where gm.GroupMemberObjectType = 201
  and gm.GroupID = 43

So my final select should look like this
select u.Name,u.UserDescription, u.UserEmail, g.GroupDescription, a.AppDescription

What I have tried:

When I tried to inner my Apps table to the original query results becomes wrong because the app id is now joining where the object is 205 instead of 201.
Updated 26-Jun-19 10:05am
MadMyche 26-Jun-19 15:06pm    
Your two statements have contradicting WHERE clauses; one is gm.GroupMemberObjectType = 201 and the other is gm.GroupMemberObjectType = 205.
Member 13806552 26-Jun-19 15:11pm    
Yeah the App table can only be joined that includes the where clause of the gm.GroupMemberObjectType being 201. That is whats' causing my issue.

1 solution

First of all, i'd strongly suggest to read this excellent article: Visual Representation of SQL Joins[^]

You probably need to use different type of join.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900