|
|
Posted by SirCodesALot on 02/01/08 17:57
On Feb 1, 11:41 am, SirCodesALot <sjour...@gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
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!
Navigation:
[Reply to this message]
|