|
|
Posted by SirCodesALot on 02/01/08 17:41
On Jan 30, 8:54 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> "SirCodesALot" <sjour...@gmail.com> wrote in message
>
> news:20a0a9bd-c8b1-4437-a601-40f1280ee966@u10g2000prn.googlegroups.com...
>
>
>
> > Select Disctinct UserInfo.Title, Webs.Title, WebGroups.Title from
>
> DISTINCT is misspelled.
>
> > Userinfo
> > INNER JOIN WebGruopMembers
>
> Above table name seems misspelled.
>
> > ON UserInfo.MemberID = WebGroupMember.MemberID
> > INNER JOIN WebGroups
> > ON Webgroups.WebId = WebGroupMember.WebID
> > INNER JOIN Webs
> > ON WebGroups.WebId = WebGroups.WebID
>
> Above join is incorrectly referencing the same column, it should be the
> column from the other table.
>
> > WHERE WebGroups.Title = "Admin"
>
> 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 Ratchevhttp://www.SQLStudio.com
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
[Back to original message]
|