Join 4 tables - can it be done?

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?

Re: Join 4 tables - can it be done?

On Jan 30, 3:07 pm, SirCodesALot wrote:
> 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?

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

Re: Join 4 tables - can it be done?

"SirCodesALot" wrote in message
news:20a0a9bd-c8b1-4437-a601-40f1280ee966@u10g2000prn.google groups.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 Ratchev
http://www.SQLStudio.com

Re: Join 4 tables - can it be done?

Look at GROUP BY

--

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




"SirCodesALot" wrote in message
news:20a0a9bd-c8b1-4437-a601-40f1280ee966@u10g2000prn.google groups.com...
>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?

Re: Join 4 tables - can it be done?

On Jan 30, 8:54=A0pm, "Plamen Ratchev" wrote:
> "SirCodesALot" wrote in message
>
> news:20a0a9bd-c8b1-4437-a601-40f1280ee966@u10g2000prn.google groups.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 =3D WebGroupMember.MemberID
> > INNER JOIN WebGroups
> > ON Webgroups.WebId =3D WebGroupMember.WebID
> > INNER JOIN Webs
> > ON WebGroups.WebId =3D WebGroups.WebID
>
> Above join is incorrectly referencing the same column, it should be the
> column from the other table.
>
> > WHERE WebGroups.Title =3D "Admin"
>
> Here is the query a bit cleaned:
>
> SELECT DISTINCT U.Title,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0W.Title,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0G.Title
> FROM WebGroups AS G
> JOIN WebGroupMembers AS M
> =A0 ON G.WebID =3D M.WebID
> JOIN Webs AS W
> =A0 ON G.WebID =3D W.WebID
> JOIN UserInfo AS U
> =A0 ON M.MemberID =3D U.MemberID
> WHERE G.Title =3D '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 =3D M.WebID
JOIN Webs AS W
ON G.WebID =3D W.ID
JOIN UserInfo AS U
ON M.MemberID =3D U.tp_ID
WHERE G.Title =3D '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

Re: Join 4 tables - can it be done?

On Feb 1, 11:41=A0am, SirCodesALot wrote:
> On Jan 30, 8:54=A0pm, "Plamen Ratchev" wrote:
>
>
>
>
>
> > "SirCodesALot" wrote in message
>
> >news:20a0a9bd-c8b1-4437-a601-40f1280ee966@u10g2000prn.googl egroups.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 =3D WebGroupMember.MemberID
> > > INNER JOIN WebGroups
> > > ON Webgroups.WebId =3D WebGroupMember.WebID
> > > INNER JOIN Webs
> > > ON WebGroups.WebId =3D WebGroups.WebID
>
> > Above join is incorrectly referencing the same column, it should be the
> > column from the other table.
>
> > > WHERE WebGroups.Title =3D "Admin"
>
> > Here is the query a bit cleaned:
>
> > SELECT DISTINCT U.Title,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0W.Title,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0G.Title
> > FROM WebGroups AS G
> > JOIN WebGroupMembers AS M
> > =A0 ON G.WebID =3D M.WebID
> > JOIN Webs AS W
> > =A0 ON G.WebID =3D W.WebID
> > JOIN UserInfo AS U
> > =A0 ON M.MemberID =3D U.MemberID
> > WHERE G.Title =3D '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,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 G.Title,
> FROM WebGroups AS G
> JOIN WebGroupMembership AS M
> =A0 ON G.WebID =3D M.WebID
> JOIN Webs AS W
> =A0 ON G.WebID =3D W.ID
> JOIN UserInfo AS U
> =A0 ON M.MemberID =3D U.tp_ID
> WHERE G.Title =3D 'Administrator'
>
> I get =A0600 results
> Example:
> Jones, Allen =A0 =A0Administrator
> SMith, David =A0 =A0Administrator
> Win, Clarence =A0 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 =A0 =A0Administrator =A0 Demo Site
> Jones, Allen =A0 =A0Administrator =A0 Other site
> Jones, Allen =A0 =A0Administrator =A0 Another sie
> Jones, Allen =A0 =A0Administrator =A0 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!

Re: Join 4 tables - can it be done?

> "SirCodesALot" wrote in message
> news:16b1e4df-ab63-4c7c-85ef-022ff89f68a4@e23g2000prf.google groups.com...

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


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

Plamen Ratchev
http://www.SQLStudio.com