SQL query join based on condition

Question

I have two tables "customer" and "activation" that I would like to join. Here are their structures:

 CUSTOMER                       Activation
 ------------                  ------------- 
 Id      Name                   EntityId       Date_Created       Type
 1       A                         1               2012            EMAIL
 2       B                         2               2011            SMS
 3       C

Now, I would like to join those two tables on customer.Id = Activation.EntityId. However, i want my final table structure to be like this:

 Id    Name    Date_Email       Date_SMS       

 1      A        2012             NULL
 2      B        NULL             2011

Basically, the columns Date_Email and Date_SMS both come from Activation.Date_Created column. IF Activation.Type is EMAIL, i set Date_Email in my final result to Date_created and set Date_SMS to null. If Activation.type is SMS, i do it the other way.

What i have right now is this:

SELECT Customer.Id, Name, Date_Created AS Date_EMail, DATE_Created AS Date_SMS 
from Customer 
inner join Activation ON Customer.Id = Activation.EntityId;

Now, i need to make a If-else condition based on Activation.Type column. I am quite new to this and I have not been able to figure this out by googling. I am using Oracle XE database btw

Can someone help me with this? Thanks

Is there only one row in Activation for each type and EntityId combination? — lc.
– lc., Commented Aug 31, 2012 at 1:07
@lc. Yes, EntityId is a foreign key refering to Id in customer table. Sorry, i should have mentioned that — CuriousCoder
– CuriousCoder, Commented Aug 31, 2012 at 1:09
@hari, are you sure that this is a good design? I'd personally discourage from doing something like that. — nothrow
– nothrow, Commented Aug 31, 2012 at 1:17

nothrow · Accepted Answer · 2012-08-31 01:09:07Z

3

SELECT Id, Name, A1.Date_Created as Date_Email, A2.Date_Created as Date_SMS FROM
CUSTOMER C
LEFT JOIN Activation A1 ON C.Id=A1.EntityID AND A1.Type='EMAIL'
LEFT JOIN Activation A2 ON C.Id=A2.EntityID AND A1.Type='SMS'

This also adds entity with ID=3, where Date_Email and Date_SMS are null. I don't know Oracle's syntax, you can remove the ID=3 with something like WHERE NOT (IsNull(Date_Email) && IsNull(Date_SMS))

answered Aug 31, 2012 at 1:09

nothrow

16.2k9 gold badges60 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lc. · Accepted Answer · 2012-08-31 01:09:31Z

2

You need to join twice, once for each type (and use a LEFT JOIN to allow nulls):

SELECT Customer.Id, Name, ActEmail.Date_Created AS Date_EMail, ActSMS.DATE_Created AS Date_SMS 
FROM Customer 
LEFT OUTER JOIN Activation AS ActEmail ON Customer.Id = ActEmail.EntityId AND ActEmail.Type = 'EMAIL'
LEFT OUTER JOIN Activation AS ActSMS ON Customer.Id = ActSMS.EntityId AND ActSMS.Type = 'SMS'

answered Aug 31, 2012 at 1:09

lc.

117k21 gold badges164 silver badges190 bronze badges

Collectives™ on Stack Overflow

SQL query join based on condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related