How can we help you today? How can we help you today?

left join with case condition

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>
kkgunner
0

Comments

1 comment

Sort by
  • 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.

    Jon_Kirkwood
    0

Add comment

Please sign in to leave a comment.