got it, heehe thanks, here is my query:
select
*
from
person as p
left join expense as e
on p.id=e.id
&& e.no=(select no from expense where no=e.no order by no desc limit 1)
----- Original Message -----
From: [EMAIL PROTECTED]
To: Eris Ristemena
Cc: mysql@lists.mysql.com
Sent: Thursday, December 22, 2005 12:32 AM
Subject: Re: Filtering join
In your last query (whichever form you use) use a LEFT JOIN instead of an
INNER JOIN and make sure you list your `person` table first.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eris Ristemena" <[EMAIL PROTECTED]> wrote on 12/21/2005 12:15:15 PM:
> Ok, that's easy, i see. But now how can i also get who hasn't done the
> expense?
>
> person
> id name
> 1 james
> 2 michael
> 3 jack
>
> expense
> no id exp
> 1 1 2000
> 2 2 1000
> 3 1 500
>
> so that the result should be:
>
> id name no exp
> 1 james 3 500
> 2 michael 2 1000
> 3 jack null null
>
>
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "Eris Ristemena" <[EMAIL PROTECTED]>
> Cc: <mysql@lists.mysql.com>
> Sent: Wednesday, December 21, 2005 11:11 PM
> Subject: Re: Filtering join
>
>
> > I can't take it any longer. Come on list this is an easy one!! We have
> > only had this question asked about once every other week this year. Shame
> > on you lurkers who knew the answer but didn't kick in. 8-(
> >
> > Eris,
> > What you are looking for can be called the "group wise maximum" because
> > you want the row with the max value for a particular group. In your case
> > (using your second example) you want the row with the maximum `no` for
> > each `id` value
> >
> > The FINE MANUAL has three ways to write queries to solve this problem:
> >
> http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
> >
> > Let us know if you have problems translating the documentation to fit your
> > needs. (for shame, for shame!)
> >
> > Merry Christmas!
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> >
> > "Eris Ristemena" <[EMAIL PROTECTED]> wrote on 12/21/2005 11:01:59 AM:
> >
> > > Ok, i change the table to make it clear.
> > >
> > > I have two tables, person and expense
> > > person
> > > id name
> > > 1 james
> > > 2 michael
> > >
> > > expense
> > > no id exp
> > > 1 1 2000
> > > 2 2 1000
> > > 3 1 500
> > >
> > > where expense.no is an autoincrement column.
> > >
> > > how can i get the last expense of each people in table person? so the
> > result
> > > should be:
> > >
> > > id name no exp
> > > 1 james 3 500
> > > 2 michael 2 1000
> > >
> > > thanks in advance
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Peter Brawley" <[EMAIL PROTECTED]>
> > > To: "Eris Ristemena" <[EMAIL PROTECTED]>
> > > Cc: <mysql@lists.mysql.com>
> > > Sent: Wednesday, December 21, 2005 10:43 PM
> > > Subject: Re: Filtering join
> > >
> > >
> > > > Eris,
> > > >
> > > > >...what i need is a distinct t1.id with maximum t2.no, so that the
> > > result
> > > > >should be like this:
> > > > >id name no id cust
> > > > >1 a 2 1 y
> > > > >2 b null null null
> > > > >3 c null null null
> > > >
> > > > To get the maximum t2.no value for each t1.id value, try ...
> > > >
> > > > SELECT
> > > > t1.id,
> > > > t1.name,
> > > > t2.id,
> > > > MAX(t2.no)
> > > > FROM t1 LEFT JOIN t2 USING (id)
> > > > GROUP BY t1.id;
> > > >
> > > > but because of the MAX() / GROUP BY aggregation, adding t2.cust to the
> > > > query will not give you the t2.cust values that go with t2.no values.
> > > >
> > > > PB
> > > >
> > > > -----
> > > >
> > > > Eris Ristemena wrote:
> > > >
> > > > >hi all,
> > > > >
> > > > >i have this small problem. I hope someone can help me out here.
> > > > >
> > > > >i have two table with one-to-many relations,
> > > > >t1
> > > > >id name
> > > > >1 a
> > > > >2 b
> > > > >3 c
> > > > >
> > > > >t2
> > > > >no id cust
> > > > >1 1 x
> > > > >2 1 y
> > > > >
> > > > >using join statement like this:
> > > > >select * from t1 left join t2 using (id)
> > > > >
> > > > >i get this result:
> > > > >id name no id cust
> > > > >1 a 1 1 x
> > > > >1 a 2 1 y
> > > > >2 b null null null
> > > > >3 c null null null
> > > > >
> > > > >but what i need is a distinct t1.id with maximum t2.no, so that the
> > > result
> > > > >should be like this:
> > > > >id name no id cust
> > > > >1 a 2 1 y
> > > > >2 b null null null
> > > > >3 c null null null
> > > > >
> > > > >Can someone help me how? group by seem doesn't work.
> > > > >
> > > > >regards,
> > > > >-ers
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > No virus found in this outgoing message.
> > > > Checked by AVG Free Edition.
> > > > Version: 7.1.371 / Virus Database: 267.14.3/209 - Release Date:
> > 12/21/2005
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
>
