What WHERE statement is needed?

[RFH Reseller: UKFlyer]

I have two tables: Fixtures & Teams

Table teams draws names from the members table and draws the date from the fixtures table.

The fixture for a particular date is displayed fine, however I also only want to only display the teams applicable for that particular date.

What is the WHERE statement that I need after FROM teams to enable this function.

The code I am using is below…..

<%
dim strID
dim strCOMMENTS
strID = Request.QueryString("ID")
dim adoST
strCOMMENTS= "Select *,(SELECT business_name FROM business_list WHERE business_id = match_sponsor_link) AS match_sponsor_link_name FROM Fixtures Where ID=" & strID
set adoST= adoCn.Execute(strCOMMENTS) %>
<div align="center">
<p align="left"><font face="Arial"><b>Match Review </b></font></p>
<center>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="700">
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Date:</font></b></td>
<td width="581" align="left"><font face="Arial" size="2"><%=adoST("date")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Details:</font></b></td>
<td width="581" align="left"><font face="Arial" size="2"><%=adoST("details")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Match Sponsor:</font></b></td>
<td width="581" align="left"><font face="Arial" size="2"><%=adoST("match_sponsor_link_name")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Location:</font></b></td>
<td width="581" align="left"><font face="Arial" size="2"><%=adoST("location")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Winners:</font></b></td>
<td width="581" align="left"><font face="Arial" size="2"><%=adoST("winners")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font face="Arial" size="2">Review:</font></b></td>
<td width="581" align="left">&nbsp;</td>
</tr>
<tr>
<td colspan="2" width="700" align="left"><font face="Arial" size="2"><%=adoST("review")%></font></td>
</tr>
<tr>
<td width="119" align="left"><b><font size="2" face="Arial">Teamsheet</font></b>:</td>
<td width="581" align="left"><font size="2"></font><b><font size="2" face="Arial"><%=adoST("teamsheet")%></font></td>
</tr>
</table>
</center>
</div><br />


<%
dim strteam_id
dim strTEAMS
strteam_id = Request.QueryString("team_id")
dim adoTM
strTEAMS= "Select *, (SELECT member_last_name FROM members WHERE member_id = member1) AS member1_name, (SELECT member_last_name FROM members WHERE member_id = member2) AS member2_name, (SELECT member_last_name FROM members WHERE member_id = member3) AS member3_name, (SELECT member_last_name FROM members WHERE member_id = member4) AS member4_name FROM teams "
set adoTM= adoCn.Execute(strTEAMS) %>


<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="700">
<tr>
<td><b><font size="2" face="Arial">Team Name</font></b></td>
<td><b><font size="2" face="Arial">Player 1</font></b></td>
<td><b><font size="2" face="Arial">Player 2</font></b></td>
<td><b><font size="2" face="Arial">Player 3</font></b></td>
<td><b><font size="2" face="Arial">Player 4</font></b></td>
</tr>
<% Do While Not adoTM.EOF%>
<tr>
<td><%=adoTM("teamname")%></td>
<td><%=adoTM("member1_name")%></td>
<td><%=adoTM("member2_name")%></td>
<td><%=adoTM("member3_name")%></td>
<td><%=adoTM("member4_name")%></td>
<%
adoTM.MoveNext
Loop%>

[RFH Reseller: nick]

Not sure I quite follow how your database is set up, but from an SQL standpoint, looking at the two tables on the pdf, and assuming that 'date' from the teams table is the fixtures table 'id', then:

SELECT teams.teamname, 
	fixtures.date
FROM teams INNER JOIN fixtures ON teams.date = fixtures.id
WHERE fixtures.date = "date here"

would select the teams whose date is the id of the date specified in the fixtures table?

[RFH Reseller: marty101]

Yes common mistake with Joins and Sub Queries. Thats not quite what they are designed for. SubQueries are more designed for in query comparisons (ie, Field "A" contains or doesn't contain), data counting (ie, output total count of apples) and outputing singular data fields that can't be paired by primary/foriegn keys (ie, daily processing flag for data) that is un-uniform.

It's also important to understand the difference between inner and outer joins, could mean the difference between having records and not having records.!

[RFH Reseller: UKFlyer]

Thanks for the advice regarding this. I have tried various combinations, however either I do not get the teams for a particular date to display at all.... or alternatively all the teams display!

This is currently what I have with at least all the teams showing...

strTEAMS= "Select *, (SELECT member_last_name FROM members WHERE member_id = member1) AS member1_name, (SELECT member_last_name FROM members WHERE member_id = member2) AS member2_name, (SELECT member_last_name FROM members WHERE member_id = member3) AS member3_name, (SELECT member_last_name FROM members WHERE member_id = member4) AS member4_name, fixtures.date, teams.fixture FROM teams INNER JOIN fixtures ON teams.fixture = fixtures.id WHERE fixture=fixtures.id"