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

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))

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

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'

Collectives™ on Stack Overflow

SQL query join based on condition

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related