Simplify a long sql statement

Hi there,
this statement works fine but I'm wondering is there any way to
simplify it?

SELECT score,
(SELECT name from players WHERE playerID=records.playerID LIMIT 1) as
playername,
(SELECT name from teams WHERE teamID=records.teamID LIMIT 1) as
teamname
FROM records

Thanks for any help!
Ciarán

On 12 May, 13:54, Ciaran <cronok...@hotmail.com> wrote:
> Hi there,
> this statement works fine but I'm wondering is there any way to
> simplify it?
>
> SELECT score,
> (SELECT name from players WHERE playerID=records.playerID LIMIT 1) as
> playername,
> (SELECT name from teams WHERE teamID=records.teamID LIMIT 1) as
> teamname
> FROM records
>
> Thanks for any help!
> Ciarán


SELECT r.score, p.name, t.name
FROM records r
LEFT JOIN players p
ON p.playerid = r.playerid
LEFT JOIN teams
ON t.team_id = r.team_id;


I'm not sure what all that "LIMIT 1" stuff is supposed to do in this
context!

On May 12, 3:35 pm, strawberry <zac.ca...@gmail.com> wrote:
> On 12 May, 13:54, Ciaran <cronok...@hotmail.com> wrote:
>
> > Hi there,
> > this statement works fine but I'm wondering is there any way to
> > simplify it?
>
> > SELECT score,
> > (SELECT name from players WHERE playerID=records.playerID LIMIT 1) as
> > playername,
> > (SELECT name from teams WHERE teamID=records.teamID LIMIT 1) as
> > teamname
> > FROM records
>
> > Thanks for any help!
> > Ciarán
>
> SELECT r.score, p.name, t.name
> FROM records r
> LEFT JOIN players p
> ON p.playerid = r.playerid
> LEFT JOIN teams
> ON t.team_id = r.team_id;
>
> I'm not sure what all that "LIMIT 1" stuff is supposed to do in this context!


Thanks Strawberry, I'll have to read up on left joins! I assumed
adding LIMIT 1 would stop the query from searching after it had found
1 match, making it a little quicker, but I suspect I might be
mistaken?

On Mon, 12 May 2008 18:06:57 +0200, Ciaran <cronoklee@hotmail.com> wrote:

> On May 12, 3:35 pm, strawberry <zac.ca...@gmail.com> wrote:
>> On 12 May, 13:54, Ciaran <cronok...@hotmail.com> wrote:
>>
>> > Hi there,
>> > this statement works fine but I'm wondering is there any way to
>> > simplify it?
>>
>> > SELECT score,
>> > (SELECT name from players WHERE playerID=records.playerID LIMIT 1) as
>> > playername,
>> > (SELECT name from teams WHERE teamID=records.teamID LIMIT 1) as
>> > teamname
>> > FROM records
>>
>> > Thanks for any help!
>> > Ciarán
>>
>> SELECT r.score, p.name, t.name
>> FROM records r
>> LEFT JOIN players p
>> ON p.playerid = r.playerid
>> LEFT JOIN teams
>> ON t.team_id = r.team_id;
>>
>> I'm not sure what all that "LIMIT 1" stuff is supposed to do in this
>> context!
>
>
> Thanks Strawberry, I'll have to read up on left joins! I assumed
> adding LIMIT 1 would stop the query from searching after it had found
> 1 match, making it a little quicker, but I suspect I might be
> mistaken?

Probably, the playerid is a PRIMARY KEY or at least unique, so the engine
is smart enough to just return the only player which can have that id.
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]