Using query from one table to query another table on same page?

[johnny]

Ok I am beating my head against the wall trying to do this.  Here are the tables I am working with:

tablename = schedule 
__________________
week  |  team  |  name
1      |    tm1  |  A
1      |    tm2  |  B
2      |  tm1  |  C
2      |    tm2  |  B

tablename = points
___________________
name    |  1    |  2 
A        |  4    |  -2
B        |  0    |  3
C        |  2    |  1


so I have a page where I want it to able to display the teams that play eachother (week1.tm1. vs week2.tms) as well as the player on each team AND how many pts they scored.  so far I can get the teams and the players pretty easily, because they are in the same table. 

what i cannot figure out is how to code it to find that in Week 1, Team 1 had player A...then take that to the points page and pull the points that A scored in Week 1.  I am currently trying this:

[code]<?php

$link = mysql_connect("xxx", "xxx", "xxx")
  or die("Could not connect : " . mysql_error());
mysql_select_db("xxx") or die("Could not select database");
$query="
SELECT points.1
FROM points.schedule
WHERE points.name= schedule.name
AND schedule.week = '1'
AND schedule.team = 'tm1'
";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res))
{
echo $row ['1'].'<br>';
}



?>[/code]

I have also tried something like:
[code]
"SELECT 1
FROM points
WHERE name = (SELECT name FROM schedule WHERE week='1' AND team='tm1')
"[/code]

Both give me "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in line xx"

I'll buy someone a keg to figure this out lol.

[king arthur]

So, the column names in the points table are 'name', '1', and '2'?

[johnny]

yes, 1 and 2 refer to week 1, week 2...

[Barand]

I thought a colname comprising only numeric digits was illegal

[johnny]

well that would be news to me and my database! lol

i have gotten other queries to work on that table, just not the one i'm asking about.

[king arthur]

Probably because the column names are not legal! Try changing them to 'one' and 'two', it's the only reason I can see why your query produces an error.

When you've done that, you can do

"select week, team, schedule.name, one, two from schedule, points where schedule.name=points.name"

then you will get the all the teams with all the points scored by the player in the team for week one and week two, week by week.

[king arthur]

Actually your first query should be
[code]
SELECT points.1
FROM points, schedule
WHERE points.name= schedule.name
AND schedule.week = '1'
AND schedule.team = 'tm1'
[/code]
Note the comma not full stop, between the table names.

[Barand]

So if column names are OK then to pull the first record from points table where the week 1 col has a value of 4 you would be using a query like

"SELECT * FROM points WHERE 1 = 4"  ???