SQL subselect question

smcelhinney

Anyone out there who could help me with this? Im looking to create an online football league generator. Alls going swimmingly until I go to create the actual league tables. I have this SQL so far.

SELECT user_id, SUM(points) AS pts, SUM(goals_for) AS gf, SUM(goals_against) AS ga FROM league_results INNER JOIN league_fixtures ON league_results.fixture_id = league_fixtures.id GROUP BY user_id

returns me all the info such as points, GF, GA etc. But I cannot figure out how to do the wins, losses, draws. there is a field in league_results called "points" which contains 3 (win), 1 (draw), or 0 (loss), so I'm making it really easy on my self. Im assuming its a subselect, but Im a bit naff at those. In pseudo code it would be something like

SELECT user, points, goals_for, goals_against, (SELECT count(points = 3) AS wins), (SELECT count(points = 1) AS draws), (SELECT count(points = 0) AS losses FROM .... 

See what Im getting at? Any ideas?

louie

first your database should allow subquery.
Another way is to do the first query in the db, then use while to step through the records one by one and make another query for the next table to get the totals togheter.
e.g
[php]
$sSlq = "select * from league_results";
//.................
while(........){
$sSql_sb = "select * from league_fixtures where id='$fixture_id'";
}//end while
[/php]

smcelhinney

thanks for the reply.

my preference is to do this using pure SQL (subselects and subqueries), not to do this programmatically. Reason being I want to eventually move these to stored procedures, and not have any SQL in code.

but thanks anyway. Anyone know how to do this using subqueries? Whats the syntax?

louie

sorry you didn't mention you are using MS Sql DB server.
Anyhow the principle are similar.there is an example:

http://www.1keydata.com/sql/sql-subquery.html

or here:

http://sql.magicmiles.com/sql-subquery.html

but if you have access to stored procedure do that instead. It's a lot faster and more secure.

cyberbob

subqueries can be nice when they work, you just have to mess around with em a bit, i usually alias the hell out of them.

another way to do what your getting at is to sum dummy indices :

select ..... sum( iif( [points] = 3 , 1 , 0 ) ) as wins....

(or whatever the corresponding syntax is in your db)

small bit cumbersome but quick and simple...

smcelhinney

You beaut Cyberbob, works a treat! Thanks!