on 09-11-2008 2:48 PM
We have been using this query to search for contacts by one part of their address. I have found that it will miss some results. If I search on a city and leave zip and county blank I should get all the results for that city regardless of whether they have a zip or county. For some BPu2019s (but not all) it will not include them unless there is something in their county field.
Am I using the u2018ANDu2019 statement and variable correctly?
SELECT T0.CardCode as 'BP Code', T0.CardName as 'BP Name', T0.CardType as 'Type',
T1.AdresType as 'Type', T1.Street, T1.Block, T1.City, T1.County, T1.ZipCode as 'Postcode', T1.Country
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T1.ZipCode like '%''[%0]''%'
AND T1.City like '%''[%1]''%'
AND T1.County like '%''[%2]''%'
Try this one:
SELECT T0.CardCode as 'BP Code',
T0.CardName as 'BP Name', T0.CardType as 'Type',
T1.AdresType as 'Type', T1.Street, T1.Block,
T1.City, T1.County, T1.ZipCode as 'Postcode', T1.Country
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE isnull(T1.ZipCode,'') like '%'+'[%0]'+'%'
AND T1.City like '%'+'[%1]'+'%'
AND isnull(T1.County,'') like '%'+'[%2]'+'%'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.