I'm using the Lookup Transformation to obtain a PhoneGuid from a Phone table. However this phone table also has another column called PhoneTypeGuid which is from another table that has different Guid's for the different phone types;
home, work, fax, mobile
From my source system I have the phone number and I know whether it's a home or work, So I've got 2 lookup's, one to return a PhoneGuid for home phone being converted and one for the work phone. What I need to do though is join this PhoneType table in the lookup so if the phone number exists as a home and work number the correct Guid is returned and it doesn't error out since it would return 2 rows.
So my lookup needs to do the following basically;
FROM ListPhoneType INNER JOIN
PhoneRawUnencrypted ON ListPhoneType.PhoneTypeUid = PhoneRawUnencrypted.PhoneTypeUid
WHERE (ListPhoneType.Name = 'home')
AND PhoneRawUnencrypted.Number = ?
Where the number is the phone number from the source system. I can only select one table from the drop down and if I put this SQL in it doesn't like it.
Any ideas how to accomplish this?