EDIT please read the following...it will help you help us help you.

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

as a start. , based on the info provided so far by the OP. here is my understanding of the sample data.

to the OP....please check this and amend/repost if required.

CREATE TABLE Child_TBL

(

App_ID INT,

Major varchar(10),

comp_id INT

);

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');

INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');

CREATE TABLE Health_TBL

(

ufId varchar(8),

sh_plan varchar(10)

);

INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('AFS','GRAPH');

INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('K7','DESIGN');

INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('DGF','CART');

INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('JKY','REDK');

INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('POL','SDRET');