left join with case condition

[Karan Nayyar]

I have three tables named by A,B and C.

Table A has below schema.

(EMAIL,FIRST_NAME,LAST_NAME)

Table B has below schema.

(EMAIL,PRODUCT_NAME,PRODUCT_NUMBER).

Table C has below schema.

(EMAIL,PRODUCT_NAME,PRODUCT_NUMBER).

I am trying to construct a sql query which can give me details like below:

From Table A,fetch first name,last name for all records in table B which have a matching record in table C WHERE
- B.EMAIL = C.EMAIL and B.PRODUCT_NAME = C.PRODUCT_NAME AND B.PRODUCT_NUMBER = C.PRODUCT_NUMBER

FINAL TABLE RECORD SHOULD LOOK LIKE BELOW with an additional column named by RESULT.

(A.FIRST_NAME,A.LAST_NAME,B.PRODUCT_NAME,B.PRODUCT_NUMBER,RESULT)

RESULT value SHOULD BE DECIDED ON below conditions:

- If record present on both table a and table b,received.
- If record present on table B but not on table A, received.
-If record present on table A but not on table B, NOT RECEIVED.

Can anyone suggest a way to construct this query? I am pretty new to sql.

I tried the below query but I am not getting it right.


SELECT DISTINCT
B.PRODUCT_NAME,B.PRODUCT_NUMBER,A.FIRST_NAME,A.LAST_NAME,
'Received' as RESULT
FROM B
INNER JOIN C on B.EMAIL = C.EMAIL
and B.PRODUCT_NAME = C.PRODUCT_NAME
and B.PRODUCT_NUMBER = C.PRODUCT_NUMBER WHERE A.EMAIL = C.EMAIL
UNION
(SELECT DISTINCT
B.PRODUCT_NAME,B.PRODUCT_NUMBER,A.FIRST_NAME,A.LAST_NAME,
'NOT Received' as RESULT
FROM B
INNER JOIN C
on B.EMAIL = C.EMAIL and B.PRODUCT_NAME != C.PRODUCT_NAME
and B.PRODUCT_NUMBER != C.PRODUCT_NUMBER WHERE A.EMAIL = C.EMAIL)