JOIN and Where clause partial match..

[Acuffdl]

I think I am getting 0 records returned... because....

I am joining the third value based on a zip code. The two tables if directly compared to each other would never have an = match.



SELECT t2.company_name, t2.firstname, t2.lastname, modelname, configname, format, version, username, t2.zip,

t2.country
FROM EtechModelRequests JOIN
CC_Host.dbo.USR_SC as t2 ON
Cast(t2.user_id As char) = username
--JOIN
--Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON PostalCode = zip
WHERE RequestDateTime > CONVERT(DATETIME, '2007-09-1 00:00:00', 102) AND interfacename LIKE '%download%' AND

result=0 AND country='CA'
--AND t3.PostalCode Like 'z1x%'
ORDER BY company_name
I was trying to do it by using a Where clause AND t3.PostalCode Like 'z1x%' that I will later turn into an Input Parameter after I get it working.

Is there anyway to trim the PostalCode to the first three characters during the join process?

Something like Sales.dbo.RF_Postal_Code_Salesman_Canada as t3 ON LEFT(PostalCode, 3) = zip

Not sure I got the LEFT function syntax correct even. Help appreciated.

[techgnome]

Yep... you can do all kinds of things in the join.... I've even used CASE statements in the join. I've also used the LIKE clause as well... So if all you are interested in is the first three, you can use the LEFT or even a LIKE... either should work.

-tg