List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:December 21 2005 3:40pm
Subject:Re: Filtering join
View as plain text  
Hello

> id    name   no   id   cust
> 1        a       2     1     y
> 2        b      null  null  null
> 3        c      null  null  null

Obviously this query should be redesigned, however it does produce
results which you want:

select id
	, name
	, no
	, cust
 from
	(select *
	from t1 left join t2 using (id)) as t3
	 where no = (
		select max(no)
		 from (select *
			from t1 left join t2 using (id)) as t3)
		 or no is null;



+------+------+------+------+
| id   | name | no   | cust |
+------+------+------+------+
|    1 | a    |    2 | y    |
|    2 | b    | NULL | NULL |
|    3 | c    | NULL | NULL |
+------+------+------+------+


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


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



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