How to use MAX and JOIN together 1

[Gooser]

I have two tables that I want to join using the max value in one as the value in the other.

[b]Tenants[/b]
TenantID  BuildingID  Combustibility
--------- ----------- ---------------
1         1           3
2         2           2
3         2           1
4         2           2
5         3           2
6         4           1
7         4           1

[b]Buildings[/b]
BuildingID  BuildingNm 
----------- -----------
1           City Center
2           Al's Diner
3           Mom's Spoon
4           Building X

The results I want are like this:

BuildingID  BuildingNm   WorstCombustibility
----------- ------------ --------------------
1           City Center  3
2           Al's Diner   2
3           Mom's Spoon  2
4           Building X   1

Where WorstCombustibility is the highest value for Combustibility in the Tenant for any given BuildingID. I can't figure out how to do this, I keep getting all 3's for WorstCombustibility.

Please help.
v/r
Gooser

 

[r937]

SELECT b.BuildingID  
     , b.BuildingNm
     , m.max_Combustibility as WorstCombustibility
  FROM Buildings AS b
INNER
  JOIN ( SELECT BuildingID
              , MAX(Combustibility) AS max_Combustibility
           FROM Tenants
         GROUP
             BY BuildingID ) AS m
    ON m.BuildingID = b.BuildingID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Gooser]

Disregard...

George sort of answered this for me the other day. A derived table is the way to go...DUH.

SELECT  Buildings.BuildingID
      , Buildings.BuildingNM
      , WorstTenant.WorstCombustibility
   FROM Buildings
   JOIN ( SELECT  MAX Ten.Combustibility AS WorstCombustibility
             FROM Tenants AS Ten
             GROUP BY Ten.BuildingID, Ten.TenantID ) AS WorstTenant
      ON Buildings.BuildingID = Tenant.BuildingID