return multiple records from join as single row

Hi

I can't seem to get my head around this join. I basically want to
select 2 records from the join as a row. let me explain.

table people: id|name;
table weights: id|week|weight;

i want to select name from people and join weights where id matches,
but I want 2 records from weights to be returned in a single row...
where weights.week=1 and weights.week=4

so if my db had the following tables

-----------
people:
----------
id = 1,
name = mike;

---------
weights
---------
id=1,
week=1,
weight=100;
...........
id=1,
week=4,
weight=120;


i would want the select to return as a single row:
mike, 100, 120


I solved it programmatically with multiple selects, but I'm sure there
is a much more elegant solution

Thanks

Mike Cardeiro

On Fri, 13 Jun 2008 20:08:08 +0200, mcardeiro@yahoo.com
<mcardeiro@yahoo.com> wrote:

> Hi
>
> I can't seem to get my head around this join. I basically want to
> select 2 records from the join as a row. let me explain.
>
> table people: id|name;
> table weights: id|week|weight;
>
> i want to select name from people and join weights where id matches,
> but I want 2 records from weights to be returned in a single row...
> where weights.week=1 and weights.week=4
>
> so if my db had the following tables
>
> -----------
> people:
> ----------
> id = 1,
> name = mike;
>
> ---------
> weights
> ---------
> id=1,
> week=1,
> weight=100;
> ..........
> id=1,
> week=4,
> weight=120;
>
>
> i would want the select to return as a single row:
> mike, 100, 120
>
>
> I solved it programmatically with multiple selects, but I'm sure there
> is a much more elegant solution

This is just a basic join?

SELECT p.name, w1.weight, w2.weight
FROM people p
JOIN weights w1
ON w1.id = p.id
AND w1.week = 1
JOIN weights w2
ON w2.id = p.id
AND w2.week = 1
WHERE p.name LIKE 'mike'

Then again, dieting is bad for the mind :P
--
Rik Wasmus
....spamrun finished