left join with case condition

kkgunner

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.

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

Jon_Kirkwood

Hi @kkgunner

 

Thank you for reaching out on the Redgate forums.

Am I correct in saying that this is not related to any of our products, but instead is a general SQL query?

 

We may not be able to provide as much assistance as perhaps a more SQL-language centric forum/site.

Having said that, looking at your query I can possibly assist with chunking down some of your logic requirements.

 

* You want to fetch all records in table B that match to table C

This can probably be covered with an INNER JOIN - it returns matches that exist in both tables

 

e.g. SELECT * from b INNER JOIN c on c.email = b.email

this would return all records from b where there is a matching email in c

 

Then using a case statement to check the following logic switches

* the records exists in both a & b

* the record exists in only b

* the record exists in only a

 

I made a simple script that looks like it may come close to your requirements. 

It returns first_name, last_name, prodct_name & product_number and does a CASE statement to work out a result value.

It does a FULL OUTER JOIN that includes all records in a & b

It does an INNER JOIN that includes only matched records in b & c

 

SELECT a.firstname, a.lastname, b.productname, b.productnumber, CASE
WHEN EXISTS (SELECT * FROM a WHERE a.email = b.email) THEN 'received - in both a & b'
WHEN a.email is NULL THEN 'received - in b but not in a'
WHEN b.email IS NULL THEN 'not received - in a but not in b'
ELSE 'null'
END AS result
FROM B

FULL OUTER JOIN a ON a.email = b.email
INNER JOIN c ON c.email = b.email

 

 

May not be a full solution, but hopefully gets you moving forward with your inquiry. 

We can't provide much other support if this is not related to a Redgate product, but may guide you into some other SQL communities who can assist further.