Select MAX DATE from joined Table

Question

I have two tables, PROJECT and BILLINGS, as follows:

Project_ID  Project_Client
----------  ------------------
P1          ABC Company
P2          DEF Company
P3          GHI Company

Proj_ID     Bill_Date   Bill_Amount
----------  ----------- -----------
P1          01-01-2013  123.45
P1          11-01-2013  234.56
P2          08-15-2013  345.67
P2          09-15-2013  456.78
P3          07-31-2013  567.89

I want to return the list of Projects and Project Clients only for projects that have billings AFTER 09-01-2013. How do I do the proper JOIN to do this?

Answer 1

If you want to get just project id and clients (which are both on project table) there is no need to join Billings :

SELECT p.Project_ID, p.Project_Client 
FROM  PROJECT p 
    WHERE EXISTS 
      (
         SELECT NULL FROM BILLINGS b WHERE b.Project_ID = a.Project_ID 
         AND b.Bill_Date > 'your_date'
      ) 

Answer 2

Try:

SELECT
    a.[Project ID]
    , a.[Project Client]
FROM PROJECT a
INNER JOIN BILLINGS b
    ON a.[Project ID] = b.[Proj_ID]
WHERE b.[Bill Date] > '09-01-2013'