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] > > > > > >
