SQL problem

[eluser]meigwilym[/eluser]
Hi,

I'm building an app for my Rugby Club website.

I've got a table with fixture information:

Code:

id | team_id | fix_date | versus | venue | result | comp | report

I've got a query to get the next fixture for a particular team:

Code:

$sql = "
SELECT fix_date, versus, venue, comp
FROM fixtures
WHERE fix_date >= NOW() AND team_id = ".$team_id."
LIMIT 1;";

but I want a query to get the next fixture for each of the 5 teams. I've tried DISTINCT, but this isn't quite what I want. Something like

Code:

$sql = "
SELECT fix_date, versus, venue, comp
FROM fixtures
WHERE fix_date >= NOW() ;";
# removed LIMIT = 1

but of course this will return all records with a date in the future.

Any ideas?

Thanks,

Mei

[eluser]Terw[/eluser]
What about IN or BETWEEN?

Code:

$sql = "
SELECT fix_date, versus, venue, comp
FROM fixtures
WHERE fix_date >= NOW() AND team_id IN(1, 4, 7)
LIMIT 1;";

[eluser]dmorin[/eluser]
You want:

Code:

$sql = "
SELECT fix_date, versus, venue, comp
FROM fixtures
WHERE fix_date >= NOW()
ORDER BY fix_date
LIMIT = 5";

This will sort them in chronological order and then get the first 5.

Edit: ah I didn't read what you were looking for carefully enough. This will get the next 5 fixtures, but not for each of the 5 teams. That's more complicated...

[eluser]meigwilym[/eluser]
Just to clarify, the club has more than one team, and all fixtures are in this table. I want to get 1 fixture for each team, so similar to "SELECT DISTINCT team_id...". So yea, your query would return the first 5 records, which may or may not contain a distinct `team_id` column.

I hope this clears up any ambiguity.

Thanks,

Mei

[eluser]narkaT[/eluser]
did you try GROUP BY team_id?

[eluser]dmorin[/eluser]
Not sure if this will work or if it will give you errors about not specifying each select field in the group by clause.

Code:

SELECT fix_date, versus, venue, comp
FROM fixtures
WHERE fix_date >= NOW()
GROUP BY team_id
HAVING min(fix_date)

[eluser]meigwilym[/eluser]
dmorin, you're a star. That worked great (I just added team_id to the SELECT portion).

I'd looked at HAVING, but I didn't know that MIN etc could be used within it (http://dev.mysql.com/doc/refman/5.0/en/g...tions.html).

Thanks again,

Mei