From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Postgresql Novice List <pgsql-novice(at)postgresql(dot)org> |
Cc: | Mike Anderson <msasjs(at)yahoo(dot)co(dot)uk> |
Subject: | Fantasy Football complex select |
Date: | 2002-05-29 09:18:14 |
Message-ID: | 20020529091813.GB2486@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have the task of designing the office fantasy football system to run
on our intranet. The fantasy football game is a game centering around
the world cup football tournament.
The game is to make up a football team of 11 players where no more than
one country is represented, and no more or less than 1 goalkeeper, 4
full-backs, 4 midfielders and 2 forwards.
I'm having trouble making a selection from a join between the players
and team tables, based on the selections table. The selection is for a
particular "team"
Task A.
I need to return all the rows in players NOT matching the following
criteria based on the selections in the selections table:
1. countries of existing selections
2. goalkeeper if a goalkeeper selected
3. full-back if 4 full-backs selected
4. midfielders if 4 full-backs selected
5. forwards if 2 forwards selected
I have managed 1. by doing:
SELECT * FROM
players
WHERE country <> ALL (
SELECT p.country FROM
players p, selections s
WHERE s.id_team = 2 AND s.id_player = p.code
)
ORDER BY country;
For 2-5 I can do the following selection, but don't know how to only
return values for postion if count of GOALKEEPERS is 1, count of
FORWARDS is 2, etc.
SELECT DISTINCT p.position, count(*)
FROM selections s, players p
WHERE s.id_team = 2 AND s.id_player = p.code
GROUP BY p.position;
position | count
-------------+-------
FORWARDS | 2
GOALKEEPERS | 1
MIDFIELDERS | 3
Task B.
I'd like to sort the output on position in players.position in the
following order "GOALKEEPERS, FULL-BACKS, MIDFIELDERS, FORWARDS". How
can I specify a custom sort order? (I'm not sure what the order by
'expression' means in the \h help).
Huge thanks for any help!
Cheers
Rory
table "players" (excerpt from 736 rows)
position | code | name | country | caps | goals | points
-------------+------+------------------+---------+------+-------+--------
FULL-BACKS | 2204 | ODaf | SEN | 29 | 0 | 0
FORWARDS | 6060 | Edilson | BRA | 11 | 4 | 0
GOALKEEPERS | 1001 | GBurgos | ARG | 35 | 0 | 0
FULL-BACKS | 2303 | SCherundolo | USA | 0 | 0 | 0
MIDFIELDERS | 4296 | DMBeasley | USA | 7 | 1 | 0
FORWARDS | 6001 | GBatistuta | ARG | 74 | 55 | 0
table "teams"
id | name | members | ts_created
----+----------+------------------+-------------------------------
1 | fantasy1 | geoff rory jenny | 2002-05-28 22:30:50.223285+01
2 | tastics | patrick uri nina | 2002-05-28 22:31:58.82615+01
table "selections"
id_team | id_player | hidden | ts_timestamp
---------+-----------+--------+-------------------------------
2 | 1082 | 0 | 2002-05-28 22:35:22.956204+01
2 | 6098 | 0 | 2002-05-28 22:35:58.677597+01
2 | 6167 | 0 | 2002-05-28 22:36:05.445792+01
2 | 4023 | 0 | 2002-05-28 22:37:12.909721+01
2 | 4282 | 0 | 2002-05-28 22:37:20.489276+01
2 | 4016 | 0 | 2002-05-28 22:37:28.286024+01
--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2002-05-29 14:33:21 | Re: Fantasy Football complex select |
Previous Message | Joshua b. Jore | 2002-05-28 23:06:36 | Re: Changing ownership of objects |