[TriLUG] OT: a SQL puzzler

[Alan Porter]

> JOIN finishers AS first ON (races.id=first.id AND first.place = 1)
> JOIN finishers AS second ON (races.id=second.id AND second.place = 2)
> JOIN finishers AS third ON (races.id=third.id AND third.place = 3)

Thanks for all of the replies... this is the direction that I
was headed, and now I have it working (although it's not races,
it's call flows in a phone switch).

Kevin, the reason for using this complex query is that I want to
also add a limit clause.  In my example, it would be like showing
only XX number of races at a time.  I'd rather do all of the database
lookups in a single query than have the application look up XX number
of calling parties (races) and then YY number of call flow scenarios
(winners) for each one.  I figure databases are really good at this
sort of thing (and PHP is not).

I'll try it several ways and see what works best.  It nothing else,
I will win the admiration (disdain) of my fellow developers who
follow behind me to maintain my code.  They will praise (curse) my
name!

Alan






.