[thelist] SQL and Bad Database design.

[Paul Cowan]

Hi Michael,

> For example, the page requires that I extract
> "stat1","stat2","stat3","stat4","stat5" and sort the players in that
> order.  The problem I have at the moment is that I can't seem to get the
> output to sort correctly after the first column.

This is a bit nasty, if there are a fixed number of well-known stats you
could get around it (in a very bodgy fashion) by something like (SQL Server
syntax here, your DB syntax might vary obviously):

	SELECT
		Player.Name, 
		StatsHandballs.stat_no as Handballs,
		StatsKicks.stat_no as Kicks,
		StatsMarks.stat_no as Marks,
		StatsFreesAgainst.stat_no as FreesAgainst,
		...
	FROM	Player
	LEFT JOIN stats AS StatsHandballs ON
		(stats.playerid = player.playerid) AND (stats.stat_id = 1)
	LEFT JOIN stats AS StatsKicks ON
		(stats.playerid = player.playerid) AND (stats.stat_id = 2)
		...
	ORDER BY
		StatsHandballs.stat_no DESC,
		StatsKicks.stat_no DESC,
		...

In other words, join once onto the stats tables for each stat... performance
might be a bit of a pooch though (good indexing would be the key here).

So what you'd get out would be:
	Name		Handballs	Kicks	...
	--------	---------	------
	Buckley	  15		  31
	Fraser	  21		  15
	...
	Carey		  3		  1
etc. 

Good luck.

<aussie>Guess where my allegiances lie...</aussie>

Paul

(ps: <aussie><aussie><aussie><oy /><oy /><oy /></aussie></aussie></aussie>?
)