Joining 3 table with MAX statement

temp2escape

table_A

id_A name

1 Max
2 Nick
3 John

table_B

id_B id_A idissue_id
1 1 1

2 1 3
3 1 4
4 2 1
5 3 5
6 3 6

table_C

id_C issue_name

1 Jan 2009
2 Mac 2009
3 Jun 2009
4 Aug 2009
5 sep 2009
6 Nov 2009

Hi guys, I'm trying to figure this out. No achievment yet.

table_A is the main data table, table_B is the issued subscribe table and table_C is the issue name table. I'm supposed to link this three table on a search query. Showing only the latest subscribed issue on each person.

Something like:

SELECT table_A.name, table_C.issue_name
FROM table_A
LEFT JOIN table_B ON table_A.id_A = table_B.id_A
LEFT JOIN table_C ON table_B.idissue_id = table_C.id_C
WHERE
table_A.name LIKE '%'

I think I'm supposed to put MAX(idissue_id) somewhere...

Please help.

Lars_Berg

select table_A.name, 
       table_C.issue_name
  from table_A
  join table_B
    on table_A.id_A = table_B.id_A
  join (select id_a,
               max(idissue_id) as idissue_id
          from table_B
         group by id_a) dt
    on dt.id_A = table_B.id_A
   and dt.idissue_id = table_B.idissue_id
  join table_C 
    on dt.idissue_id = table_C.id_C

temp2escape

Thank you very much. It works.

Is there any link where I can learn more on this?

johanafm

Any dbms reference would have information on join syntax, and most likely examples with nested queries as well. e.g. MySQL's is here. The same goes for aggreagate functions and the group by clause is described in the SELECT syntax section, and possibly also among the aggregate functions.