List:General Discussion« Previous MessageNext Message »
From:Eris Ristemena Date:December 21 2005 5:42pm
Subject:Re: Filtering join
View as plain text  
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: SGreen@stripped 
  To: Eris Ristemena 
  Cc: mysql@stripped 
  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" <eris@stripped> 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: <SGreen@stripped>
  > To: "Eris Ristemena" <eris@stripped>
  > Cc: <mysql@stripped>
  > 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" <eris@stripped> 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" <peter.brawley@stripped>
  > > > To: "Eris Ristemena" <eris@stripped>
  > > > Cc: <mysql@stripped>
  > > > 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/mysql?unsub=1
  > > >
  > >
  > 

Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec