SQL - Join part of one table into another, based on two columns matching

I have two tables and I am looking to merge data from one table into another. Specifically, Table_A is millions of records of patient discharge's from Hospitals; table_B tells whether a particular hospital is labeled as an Acute Care Facility. I'm wanting to grab all records from table A where the Hospital is an Acute Care Facility.

Table A has many fields; of relevance:

HOSPITAL_ID | YEAR_AND_QUARTER | RECORD_ID

With record ID being unique. There are hundreds to thousands of record's (RECORD_ID's) per HOSTPIAL_ID, and hundreds of HOSPITAL_IDs per YEAR_AND_QUARTER

Table_B has a few fields:

HOSPITAL_ID | YEAR_ALONE | ACUTE_INDICATOR
1223 | 2004 | X  
1223 | 2005 | X  
1289 | 2004 |  
1289 | 2005 | X  

With Hospital_ID AND Year occuring only once together.

I can't join on Hospital_ID, because in Table B each Hospital ID occurs more than once. Also, table_B lumps all quarterly data into one year (instead of 2004Q1, 2004Q2.. only 2004).

Thus the final output (preferably as a new t开发者_StackOverflow社区able) I want is just ACUTE_INDICATOR added to Table_A

HOSPITAL_ID | YEAR_AND_QUARTER | ACUTE_INDICATOR | RECORD_ID....

Appologies ahead, I'm an SQL infant and wasn't even quite sure what to search for an answer. My best guesses were (pseudo):

INNER JOIN (SELECT B.ACUTE_INDICATOR)
ON A.HOSPITAL_ID = B.HOSPITAL_ID
WHERE LEFT(A.YEAR_AND_QUARTER,4) = B.YEAR_ALONE

Many thanks :)

---

This will create the new table for you:

SELECT
    a.HOSPITAL_ID,
    a.YEAR_AND_QUARTER,
    b.ACUTE_INDICATOR,
    a.RECORD_ID
INTO c
FROM
    a JOIN
    b ON a.HOSPITAL_ID = b.HOSPITAL_ID 
        AND LEFT(a.YEAR_AND_QUARTER, 4) = b.YEAR_ALONE

Then if you wanted to query that table for Acute Care Facilities only...

SELECT * FROM c WHERE ACUTE_INDICATOR = 'x'

---

I would just use EXISTS for this:

<your select from table A>
FROM TableA A
WHERE EXISTS (SELECT 1
              FROM TableB B
              WHERE A.HOSPITAL_ID = B.HOSPITAL_ID
              AND LEFT(A.YEAR_AND_QUARTER,4) = B.YEAR_ALONE
              AND b.Acute-Indicator = 'X')

This won't give you any duplicate rows if there are 1000s per hospital in table B but still filters the way you want.