Joining Data from multiple tables using DRW

Thanks Kathleen, I managed to do that with your help..

My next step is that I have a page with multiple choices:

Team 1
Team 2 etc.

and when I select say Team 1 I go to a summary page which I want to show
only data associated with team 1, but using a custom SQL I keep getting all
Teams..

I have been very succesful doing this with a single table as the DRW helps
greatly, but need help to do it with joining tables using a custom sql code.

John P.




"Kathleen Anderson" <kanderson.RemoveThis@remove.mvps.org> wrote in message
news:eGVheRKXJHA.4960@TK2MSFTNGP02.phx.gbl...
> You should be able to do it the same way you do with one table. The only
> thing that won't work is Lookup fields. To get a jump start, write your
> query in Access and copy and paste the SQL into the DRW.
>
> --
>
> ~ Kathleen Anderson
> Microsoft MVP - FrontPage
> Spider Web Woman Designs
> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
> Expression Web Wiki: http://expression-web-wiki.com/
> FrontPage Resources: http://www.spiderwebwoman.com/resources/
> Please reply to the newsgroup for the benefit of others
>
>
> "John P." <parkejo.RemoveThis@gmail.com> wrote in message
> news:ur2G3GKXJHA.5328@TK2MSFTNGP05.phx.gbl...
>> Is there any way to create a results page combining data from multiple
>> tables in the same database?
>>
>> John P.
>>
>
>

Glad you made it this far.

You need to add a WHERE clause to your SQL:
http://www.w3schools.com/sql/sql_where.asp

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
Expression Web Wiki: http://expression-web-wiki.com/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others


"John P." <parkejo.DeleteThis@gmail.com> wrote in message
news:emFVPLjXJHA.2280@TK2MSFTNGP06.phx.gbl...
> Thanks Kathleen, I managed to do that with your help..
>
> My next step is that I have a page with multiple choices:
>
> Team 1
> Team 2 etc.
>
> and when I select say Team 1 I go to a summary page which I want to show
> only data associated with team 1, but using a custom SQL I keep getting
> all Teams..
>
> I have been very succesful doing this with a single table as the DRW helps
> greatly, but need help to do it with joining tables using a custom sql
> code.
>
> John P.
>
>
>
>
> "Kathleen Anderson" <kanderson.DeleteThis@remove.mvps.org> wrote in message
> news:eGVheRKXJHA.4960@TK2MSFTNGP02.phx.gbl...
>> You should be able to do it the same way you do with one table. The only
>> thing that won't work is Lookup fields. To get a jump start, write your
>> query in Access and copy and paste the SQL into the DRW.
>>
>> --
>>
>> ~ Kathleen Anderson
>> Microsoft MVP - FrontPage
>> Spider Web Woman Designs
>> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
>> Expression Web Wiki: http://expression-web-wiki.com/
>> FrontPage Resources: http://www.spiderwebwoman.com/resources/
>> Please reply to the newsgroup for the benefit of others
>>
>>
>> "John P." <parkejo.DeleteThis@gmail.com> wrote in message
>> news:ur2G3GKXJHA.5328@TK2MSFTNGP05.phx.gbl...
>>> Is there any way to create a results page combining data from multiple
>>> tables in the same database?
>>>
>>> John P.
>>>
>>
>>
>
>

Thanks again Kathleen, I am sure I am getting close but still no luck.

The statement I have (which verifies okay) is

SELECT teams.ID, teams.*, sponsors.*
FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
WHERE sponsors.teamid=teams.ID

But I still get all the teams..

I have shown the sample here: http://rightfast.com/test1/ and if you
select a team you can see the result.

Thanks
John P.



"Kathleen Anderson" <kanderson DeleteThis @remove.mvps.org> wrote in message
news:OLtdEhjXJHA.5064@TK2MSFTNGP02.phx.gbl...
> Glad you made it this far.
>
> You need to add a WHERE clause to your SQL:
> http://www.w3schools.com/sql/sql_where.asp
>
> --
>
> ~ Kathleen Anderson
> Microsoft MVP - FrontPage
> Spider Web Woman Designs
> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
> Expression Web Wiki: http://expression-web-wiki.com/
> FrontPage Resources: http://www.spiderwebwoman.com/resources/
> Please reply to the newsgroup for the benefit of others
>
>
> "John P." <parkejo DeleteThis @gmail.com> wrote in message
> news:emFVPLjXJHA.2280@TK2MSFTNGP06.phx.gbl...
>> Thanks Kathleen, I managed to do that with your help..
>>
>> My next step is that I have a page with multiple choices:
>>
>> Team 1
>> Team 2 etc.
>>
>> and when I select say Team 1 I go to a summary page which I want to show
>> only data associated with team 1, but using a custom SQL I keep getting
>> all Teams..
>>
>> I have been very succesful doing this with a single table as the DRW
>> helps greatly, but need help to do it with joining tables using a custom
>> sql code.
>>
>> John P.
>>
>>
>>
>>
>> "Kathleen Anderson" <kanderson DeleteThis @remove.mvps.org> wrote in message
>> news:eGVheRKXJHA.4960@TK2MSFTNGP02.phx.gbl...
>>> You should be able to do it the same way you do with one table. The only
>>> thing that won't work is Lookup fields. To get a jump start, write your
>>> query in Access and copy and paste the SQL into the DRW.
>>>
>>> --
>>>
>>> ~ Kathleen Anderson
>>> Microsoft MVP - FrontPage
>>> Spider Web Woman Designs
>>> Expression Web Resources: http://www.spiderwebwoman.com/xweb/
>>> Expression Web Wiki: http://expression-web-wiki.com/
>>> FrontPage Resources: http://www.spiderwebwoman.com/resources/
>>> Please reply to the newsgroup for the benefit of others
>>>
>>>
>>> "John P." <parkejo DeleteThis @gmail.com> wrote in message
>>> news:ur2G3GKXJHA.5328@TK2MSFTNGP05.phx.gbl...
>>>> Is there any way to create a results page combining data from multiple
>>>> tables in the same database?
>>>>
>>>> John P.
>>>>
>>>
>>>
>>
>>
>
>

Of course you get them all. Your WHERE clause would only exclude teams
that doesn't have a sponsor.
You should use the specifik ID of the team you want results from not
all IDs as you do below. Something like:
SELECT teams.ID, teams.*, sponsors.*
FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
WHERE sponsors.teamid="team1"

Regards Jens Peter Karlsen.

On Mon, 15 Dec 2008 12:46:49 -0500, "John P." <parkejo.DeleteThis@gmail.com>
wrote:

>Thanks again Kathleen, I am sure I am getting close but still no luck.
>
>The statement I have (which verifies okay) is
>
>SELECT teams.ID, teams.*, sponsors.*
>FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
>WHERE sponsors.teamid=teams.ID
>
>But I still get all the teams..
>

The OP is using link parameters from index.asp
So in the top of the team.asp page he needs
<%
TeamID=Request.QueryString("ID")
'add check to send them back if no team id in query string
IF Len(TeamID)=0 Then Response.Redirect "index.asp"
%>

Then the SQL would be
SQL="SELECT teams.ID, teams.*, sponsors.* FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid WHERE sponsors.teamid=" &
TeamID


See http://www.spiderwebwoman.com/thingumajig/tweaks.htm#passing

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


"Jens Peter Karlsen" <jpkarlsen RemoveThis @mvps.org> wrote in message news:366ek4dgflhpd3lt3s400dtt70qu4cpgrf@4ax.com...
| Of course you get them all. Your WHERE clause would only exclude teams
| that doesn't have a sponsor.
| You should use the specifik ID of the team you want results from not
| all IDs as you do below. Something like:
| SELECT teams.ID, teams.*, sponsors.*
| FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
| WHERE sponsors.teamid="team1"
|
| Regards Jens Peter Karlsen.
|
| On Mon, 15 Dec 2008 12:46:49 -0500, "John P." <parkejo RemoveThis @gmail.com>
| wrote:
|
| >Thanks again Kathleen, I am sure I am getting close but still no luck.
| >
| >The statement I have (which verifies okay) is
| >
| >SELECT teams.ID, teams.*, sponsors.*
| >FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
| >WHERE sponsors.teamid=teams.ID
| >
| >But I still get all the teams..
| >

There would be a performance gain by turning this sql statement into a view


"Stefan B Rusynko" <sbr_enjoy.DeleteThis@hotmail.com> wrote in message
news:ueWVb%231XJHA.5108@TK2MSFTNGP05.phx.gbl...
> The OP is using link parameters from index.asp
> So in the top of the team.asp page he needs
> <%
> TeamID=Request.QueryString("ID")
> 'add check to send them back if no team id in query string
> IF Len(TeamID)=0 Then Response.Redirect "index.asp"
> %>
>
> Then the SQL would be
> SQL="SELECT teams.ID, teams.*, sponsors.* FROM teams INNER JOIN sponsors
> ON teams.ID = sponsors.teamid WHERE sponsors.teamid=" &
> TeamID
>
>
> See http://www.spiderwebwoman.com/thingumajig/tweaks.htm#passing
>
> --
>
> _____________________________________________
> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> "Warning - Using the F1 Key will not break anything!" (-;
> _____________________________________________
>
>
> "Jens Peter Karlsen" <jpkarlsen.DeleteThis@mvps.org> wrote in message
> news:366ek4dgflhpd3lt3s400dtt70qu4cpgrf@4ax.com...
> | Of course you get them all. Your WHERE clause would only exclude teams
> | that doesn't have a sponsor.
> | You should use the specifik ID of the team you want results from not
> | all IDs as you do below. Something like:
> | SELECT teams.ID, teams.*, sponsors.*
> | FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
> | WHERE sponsors.teamid="team1"
> |
> | Regards Jens Peter Karlsen.
> |
> | On Mon, 15 Dec 2008 12:46:49 -0500, "John P." <parkejo.DeleteThis@gmail.com>
> | wrote:
> |
> | >Thanks again Kathleen, I am sure I am getting close but still no luck.
> | >
> | >The statement I have (which verifies okay) is
> | >
> | >SELECT teams.ID, teams.*, sponsors.*
> | >FROM teams INNER JOIN sponsors ON teams.ID = sponsors.teamid
> | >WHERE sponsors.teamid=teams.ID
> | >
> | >But I still get all the teams..
> | >
>
>