join table where table1.name = table2.team_name

[phphit]

Basically my table structure are as below.

Teams Table

id int(11)
team_name varchar(100)

schedule Table

id int(11)
team1 varchar(100)
team2 varchar(100)

Now,

Basically I want to random 10 team list from table teams (field team_name).

What I want to do here is that want to count the no. of matches that would be played by that team. (Basically count in other team based on other table).

So basically, want to get list of team_name with count info from table schedule where teams.team_name = schedule.team1 or schedule.team2.

Help would be appreciated.

[vibhaJ]

Here is a query.
Try it.

SELECT teams.name, count( teams.name )
FROM teams
LEFT JOIN schedule ON schedule.team1 = teams.name
OR schedule.team2 = teams.name
WHERE schedule.id >0
GROUP BY teams.name
ORDER BY rand( )
LIMIT 0 , 10