List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 21 2005 5:32pm
Subject:Re: Filtering join
View as plain text  
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