I have done a few table joins and below is how my data looks.
Cust_No Account_No Product_H_f Product_H_L
123 A9023 Core Training
123 A9023 Core Training
834 A9023 Core Complimentary
634 A9024 Core Complimentary
264 A9025 DTB Training
263 A9025 DTB Complimentary
677 A9025 DTB Training
I want to add a column to this result that would be called 'Customer_no who have Training' and should look like this. (My original query is pretty complex with joins on 6 tables using left join)
Cust_No Account_No Product_H_f Product_H_L Cust_has_Training
123 A9023 Core Training Yes
123 A9023 Core Training Yes
834 A9023 Core Complimentary Yes
834 A9023 Core Training Yes
634 A9024 Core Complimentary No
264 A9025 DTB Training Yes
263 A9025 DTB Complimentary No
677 A9025 DTB Training Yes
677 A9025 DTB Basic Yes
I am using the below code to achieve this and it seems to be working. But since I have 48M records, it is taking forever to run. Is there a way I can optimize this query?
SELECT Cust_No,
Account_No,
Product_H_f,
Product_H_L,
(CASE WHEN Cust_No IN (SELECT distinct(Cust_No) from table where
Product_H_L='Training') then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE