SQL problem - Printable Version

SQL problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL problem (/showthread.php?tid=13276)

SQL problem - El Forum - 11-17-2008

[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

SQL problem - El Forum - 11-17-2008

[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;";

SQL problem - El Forum - 11-17-2008

[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...

SQL problem - El Forum - 11-17-2008

[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

SQL problem - El Forum - 11-17-2008

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

SQL problem - El Forum - 11-17-2008

[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)

SQL problem - El Forum - 11-17-2008

[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/group-by-functions.html).

Thanks again,

Mei