Insert based on conditions

Transact-SQL
1

Hello, I have 2 tables: Child_Table and Health_Table:

Child_Table has columns used: App_ID, Major
Health_Table has columns used: Re_ID, Plan

Sample data:
Child Table

_App_ID_             _ Major_
004540036             GEBOX
004540036             GEBOX
004540036             GEBOX
004540036             GEBOX

Sample data:

_Health Table_ 
**Re_ID**                 **Major**
U1                         GECCE
U2                        REDSG
U5                        GFRTY
U7                        GEBOX
U8                       JKIUTY

Sample Output Needs to be in Temp Table

** MUST SKIP Re_ID for this record but may be used for the next App_ID if Major does not match in both tables

App_ID                Re_ID
004540036              U1
004540036              U2
004540036              U5
004540036              U8

I need to insert the App_ID and Re_ID into a temp table WHERE the Major and Plan can not be the same. Also each App_ID will be in the table 4 times and needs to have a different RE_ID assigned. Any ideas?

2

Please show us sample data to make your question clear.

3

I edited my post. Thank you.

4

How are the tables related? Do you just want the cartesian product when there's no match on major/plan?

5

The are not related, but they both have a major column that determines if they are assigned to each other. For example, it is like a review board, if App_ID (004540036) has a major of GEBOX, then the RE_ID in the Health_Table that has a Major of GEBOX can can not be assigned to that APP_ID when being inserted into the temp table. I need to loop through the entire Child table and Health and assign the records into the temp table.