Help with Join

[Skipjackrick]

I am trying to get the largest fish that each angler caught but also associate the correct data in the row.

 

With the following code I get an error message

column 'angler' is ambiguous

 

Do you know how to fix that?  I am still learning this join and on stuff.

select angler, 
       team_id, 
       species_id, 
       length, 
       image
  from submit
  join (select angler,
               max(length) as mlength
          from submit
         group by angler) dt
    on (submit.angler,submit.length) = (dt.angler,dt.mlength)
where submit.region_id = 1
order by submit.species_id

[awjudd]

angler is being returned by both the submit table and the subquery.  In order to fix that you just need to qualify the angler which is in the outermost select statement (i.e. dt.angler)

 

~judda

[Skipjackrick]

angler is being returned by both the submit table and the subquery.  In order to fix that you just need to qualify the angler which is in the outermost select statement (i.e. dt.angler)

 

~judda

 

Thanks..

 

So I've rearranged it a little bit...It seems to work but its only returning 7 rows.  When there should be like 15 rows because there are more species than that.  Do you happen to know why it might be doing that?

 

<?php
      //Query for records region 1
      $records1 = "SELECT 
                       angler
                       ,team_id
                       ,dt.species_id
                       ,length
                       ,image
                       FROM submit
                         join (SELECT 
                                  species_id
                                  ,max(length) as mlength
                               FROM submit
                                GROUP BY species_id
                               ) dt
                           ON (submit.species_id,submit.length) = (dt.species_id,dt.mlength)
                        WHERE submit.region_id = 1
                        ORDER BY submit.species_id
                ";
      $records_result1 = mysql_query($records1) or die(mysql_error());
?>

[awjudd]

I'm guessing that the right table doesn't return the 8 extra rows you were expecting or something along those lines.

 

~judda

[Skipjackrick]

I'm guessing that the right table doesn't return the 8 extra rows you were expecting or something along those lines.

 

~judda

 

Right table?

 

I am only using one table named Submit.

[awjudd]

Right table = the table on the right hand side of the join.

 

Left table = Submit

Right table = select angler,

              max(length) as mlength

          from submit

        group by angler

 

~judda

[Skipjackrick]

OH!!!

 

I get it...My WHERE statement should also be in the right table.  I hope that works.

 

Right table = the table on the right hand side of the join.

 

Left table = Submit

Right table = select angler,

              max(length) as mlength

          from submit

        group by angler

 

~judda