query woes

[vampke]

Hello peoples,

 

I trying to get this query to work but I'm stuck like a madman in a straitjacket :/

 

I have 2 tables: teams and schedule

 

CREATE TABLE `schedule` (
  `id` int( unsigned zerofill NOT NULL auto_increment,
  `team1` varchar(50) NOT NULL default '',
  `team2` varchar(50) NOT NULL default '',
  `team1_score` varchar(5) NOT NULL default '0',
  `team2_score` varchar(5) NOT NULL default '0',
  PRIMARY KEY  (`id`)
)

 

CREATE TABLE IF NOT EXISTS `teams` (
  `id` int(4) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

 

team1 and team2 form schedule table are id's from the teams table

 

I need the results from the schedule table ordered alphabetically by team1.name and then by team2.name

 

so far I have

SELECT s.team1, s.team2, s.team1_score, s.team2_score , t.name
FROM schedule s, teams t
WHERE s.team1 = t.id
ORDER BY t.name

 

But I don't know how to get the teamname for team2 this way.

 

Can anyone help me with this?

[Ken2k7]

What is teams? There's no such table and you're selecting from it.

 

Try this

SELECT s.id AS sid, * FROM schedule s INNER JOIN zclpN_teams t ON t.id = s.team1 INNER JOIN zclpN_teams k ON k.id = s.team2 ORDER BY t.name, k.name

[revraz]

teams is his second table.

[Ken2k7]

teams is his second table.

That's because he edited his post.

[vampke]

yes i edited my OP

I have been fiddling around with your code ken, thanks for it, it was really helpful.

 

I have nearly the result i need using

SELECT s.id, s.team1, s.team2, s.team1_score, s.team2_score
FROM schedule s
INNER JOIN teams t ON t.id = s.team1
INNER JOIN teams k ON k.id = s.team2
ORDER BY t.name, k.name

 

The only thing i need now is a way to get t.name and k.name.

 

I uses the following php code:

while ($count < $total_rows) {
	$team1 = mysql_result($resultset,$count,"team1");
	$team2 = mysql_result($resultset,$count,"team2");
	$t1_score = mysql_result($resultset,$count,"team1_score");
	$t2_score = mysql_result($resultset,$count,"team2_score");

 

I tried different things to get the t.name and k.name in my result set but without luck.

 

Any ideas on this?

[Ken2k7]

You never selected them. In your SELECT statement, add t.name and k.name.

 

SELECT s.id, s.team1, s.team2, s.team1_score, s.team2_score, t.name, k.name
FROM schedule s
INNER JOIN teams t ON t.id = s.team1
INNER JOIN teams k ON k.id = s.team2
ORDER BY t.name, k.name