I need some help, i need to join 4 tables but get a huge number of
results, so I must be doing something wrong.

here are my tables

WebGroups
- WebId
-Title (values: "Admin" or "NonAdmin")

WebGroupMembers
- WebId
- MemberId

Webs
- WebId
- Title

UserInfo
-MemberId
-Title

I want to return the Distinct UserInfo.Title, Webs.Title,
WebGroups.Title where WebGroups.Title="Admin"

So here is what I thought should work

Select Disctinct UserInfo.Title, Webs.Title, WebGroups.Title from
Userinfo
INNER JOIN WebGruopMembers
ON UserInfo.MemberID = WebGroupMember.MemberID
INNER JOIN WebGroups
ON Webgroups.WebId = WebGroupMember.WebID
INNER JOIN Webs
ON WebGroups.WebId = WebGroups.WebID
WHERE WebGroups.Title = "Admin"

Any ideas?

On Jan 30, 3:07 pm, SirCodesALot <sjour... (AT) gmail (DOT) com> wrote:
Sir C:

Your joins look OK to me. Irrespective of quantity, do your results
look right? Are the returned rows indeed distinct, or are you saying
they're duplicated across your three columns? Lastly, is the only
value in title "Admin," as your query specifies?

Posting a snippet of your result set might go a long way to getting
some help.

Regards,

--Jeff

"SirCodesALot" <sjourdan (AT) gmail (DOT) com> wrote

DISTINCT is misspelled.


Above table name seems misspelled.



Above join is incorrectly referencing the same column, it should be the
column from the other table.


Here is the query a bit cleaned:

SELECT DISTINCT U.Title,
W.Title,
G.Title
FROM WebGroups AS G
JOIN WebGroupMembers AS M
ON G.WebID = M.WebID
JOIN Webs AS W
ON G.WebID = W.WebID
JOIN UserInfo AS U
ON M.MemberID = U.MemberID
WHERE G.Title = 'Admin'


HTH,

Plamen Ratchev
http://www.SQLStudio.com

Look at GROUP BY

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"SirCodesALot" <sjourdan (AT) gmail (DOT) com> wrote

On Jan 30, 8:54*pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Thank you all for the replies. here is the issue, when I add another
column name to the select, my results increase dramtically. For
example with this query:

SELECT DISTINCT U.tp_Title,
G.Title,
FROM WebGroups AS G
JOIN WebGroupMembership AS M
ON G.WebID = M.WebID
JOIN Webs AS W
ON G.WebID = W.ID
JOIN UserInfo AS U
ON M.MemberID = U.tp_ID
WHERE G.Title = 'Administrator'

I get 600 results
Example:
Jones, Allen Administrator
SMith, David Administrator
Win, Clarence Administrator

If a add W.title to the select statement to get the name of the site,
I get 16000 results
example
Example:
Jones, Allen Administrator Demo Site
Jones, Allen Administrator Other site
Jones, Allen Administrator Another sie
Jones, Allen Administrator Another

Why does adding another select column cause the Distinct on the
u.title to fail? I should have taken more DB courses

Thanks again
-SJ

On Feb 1, 11:41*am, SirCodesALot <sjour... (AT) gmail (DOT) com> wrote:
Ok, I think i found the problem. the U.tp_ID was not unique to a
single person. Thats why so many more results were happening I think.
I will keep looking, and thanks again for the replies!

DISTINCT applies to the whole row, not only to one or two columns.

Plamen Ratchev
http://www.SQLStudio.com