Topic: 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

Code: [Select]

CREATE TABLE `schedule` (
  `id` int(8) 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`)
)

Code: [Select]

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

Code: [Select]

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

Code: [Select]

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

Code: [Select]

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:

Code: [Select]

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.

Code: [Select]

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