query a pair of inputs in a table

[sarahzhou]

Hi,

I have a table(in sas server) for a job needs two teams to work on it.

TABLE A, Jobs_All

TEAM_1	TEAM_2	JOB_LIST	J_DT	J_ID
ELLA	JACK
ALICE	JIMMY	JOB1	12-01-22	J1
ALICE	JIMMY	JOB2	13-01-22	J2
CINDY	BOB	JOB3	14-01-22	J3
CINDY	BOB	JOB4	14-01-22	J4
AMBER	DANIEL	JOB5	16-01-22	J5
AMBER	DANIEL	JOB6	17-01-22	J6
KITTY	ARRON

A manage doesn't know who is team_1 or team_2, he wanted to know who did the job in the past, for the list below

TABLE B, query_list

TEAM_1	TEAM_2
	JACK
ELLA	JACK
ALICE
CINDY
	BOB
CINDY	BOB
KITTY	ARRON
CINDY	JIMMY

The expected output should look like this:

TEAM_1	TEAM_2	JOB_LIST	J_DT	J_ID
	JACK
ELLA	JACK
ALICE		JOB1	12-01-22	J1
ALICE		JOB2	13-01-22	J2
CINDY		JOB3	14-01-22	J3
		JOB4	14-01-22	J4
	BOB	JOB3	14-01-22	J3
		JOB4	14-01-22	J4
CINDY	BOB	JOB3	14-01-22	J3
CINDY	BOB	JOB4	14-01-22	J4
KITTY	ARRON
CINDY	JIMMY

I did the

left join B.TEAM_1=A.TEAM_1 ->TEAM_1_JOB (table C)

left join B.TEAM_2=A.TEAM_2 ->TEAM_2_JOB (table D)

full outer join on C.J_ID=D.J_ID

what I get is like below

TEAM_1	TEAM_2	JOB_LIST	J_DT	J_ID
ELLA	JACK
ALICE	JIMMY	JOB1	12-01-22	J1
ALICE	JIMMY	JOB2	13-01-22	J2
CINDY	BOB	JOB3	14-01-22	J3
CINDY	BOB	JOB4	14-01-22	J4
KITTY	ARRON

How can I get the expected output table which can take either one or two input columns as a join condition, or other way to do that?

Kindly advise

Thanks

[AlexBennasar]

The logic of that expected output is difficult to understand for me...why the JOB4 appears with empty TEAM1 and TEAM2, but the JOB3 appears always with at least one team member? The situation seems pretty symmetrical for both in the original tables...

[sarahzhou]

Sorry, the logic was wrong. I will figure out in a bit. @AlexBennasar , thank you!