Joining Data from multiple tables using DRW

[John P.]

Is there any way to create a results page combining data from multiple
tables in the same database?

John P.

 

[Kathleen Anderson]

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.]

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]

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.]

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.

 

[Jens Peter Karlsen]

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.

 

[Stefan B Rusynko]

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!" (-;
_____________________________________________


| 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." <[email protected]>
| 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..
| >

 

[Mike Mueller]

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

 

[John P.]

Thanks everyone, I am really getting close now. I have it reurning only for
the applicable team but is shows a complete view for each sponsor I am just
trying to show 1 view with all the sponsors available.

But again I really appreciate your help as I have climed over a couple of
barriers.

Thanks
John P.

The statement I have in place is:

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

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

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!" (-;
_____________________________________________


| 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." <[email protected]>
| 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..
| >

 

[Mike Mueller]

What I would do is take MOST of your sql statement
"SELECT teams.ID, teams.*, sponsors.* FROM teams INNER JOIN sponsors ON
teams.ID = sponsors.teamid"
and create a View in sql (or query in Access) and I'll call it
vTeamsToSponsors.

Then; in your web page coding, I would use this SQL statement
"SELECT * FROM vTeamsToSponsors WHERE sponsors.teamid=::ID::"





WHERE sponsors.teamid=::ID::"

Thanks everyone, I am really getting close now. I have it reurning only
for the applicable team but is shows a complete view for each sponsor I am
just trying to show 1 view with all the sponsors available.

But again I really appreciate your help as I have climed over a couple of
barriers.

Thanks
John P.

The statement I have in place is:

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

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

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!" (-;
_____________________________________________


| 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." <[email protected]>
| 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..
| >