JOIN MAX per Group

I have a pretty simple sql:

SELECT TRIM(Branch.Area)                             "Area", 
       Branch.Article                                "Article"
FROM   DBA.Branch Branch 
       LEFT JOIN DBA.Attribute Attribute 
              ON Branch.Article = Attribute.Article
       LEFT JOIN DBA.Attribute2 Attribute2 
              ON Branch.Article = Attribute2.Article
       LEFT JOIN DBA.ItemMaster ItemMaster 
              ON Branch.Article = ItemMaster.Article

WHERE  Branch.Type = 'M' 
       AND Branch.Area LIKE '%US%' 

As you can see this query provides a table out of the intersection based on 4 tables. The selected columns are much more than 2. But these are not necessary here.

Now I would like the cost information per Article out of a 5th table. This table has this structure:

• Article
• Department
• Type
• Cost

For each code there a differents costs for per department. That means every article is listed several times. Once per Department. Now I am looking for the MAX cost for each Article. This value I would like to add to the query above. But how?

There are also two filters for the Cost table:

• Cost.Type = ‘M’
• Cost.Area LIKE ‘%US%’

Answer

A solution is

SELECT TRIM(Branch.Area)                             "Area", 
       Branch.Article                                "Article",
       C.maxcost
FROM   DBA.Branch Branch 
       LEFT JOIN DBA.Attribute Attribute 
              ON Branch.Article = Attribute.Article
       LEFT JOIN DBA.Attribute2 Attribute2 
              ON Branch.Article = Attribute2.Article
       LEFT JOIN DBA.ItemMaster ItemMaster 
              ON Branch.Article = ItemMaster.Article
       LEFT JOIN (SELECT Article, MAX(Cost) AS maxcost FROM DBA.ItemMaster.Cost GROUP BY Article) C 
              ON C.Article=Branch.Article

WHERE  Branch.Type = 'M' 
       AND Branch.Area LIKE '%US%' 

I don’t really get this

There are also two filters for the Cost table:

Cost.Type = 'M'
Cost.Area LIKE '%US%'

It looks like exactly the same filters as on the branch table. If you have the same article information stored on the 2 tables, then its not necessary to filter the Cost records. If not, you can do this in the additional jointure:

       LEFT JOIN (SELECT Article, MAX(Cost) AS maxcost FROM DBA.ItemMaster.Cost WHERE Type='M' AND Area LIKE '%US%' GROUP BY Article) C 
              ON C.Article=Branch.Article