Multiple Fuzzy Lookup

Is it possbile to have multiple fuzzy lookup within a data flow?

I need to have at least 3 fuzzy lookup in a data flow. Here're the conditions that I try to find match: 1=Zip&City, 2=Zip&State, 3=City&State. I've the first fuzzy lookup working fine. After that, I've a conditional split to get any unmatch, then use another fuzzy lookup for a second condition...at this point, I get the error saying "The package contains two objects with duplicate name of output column _Similarity..." I do not need to get the _Similarity and _Confidence, so is there a way to exclude them from returning in the output?

Any comments?

Thanks in advance.

Ash,

Using the Advanced Editor for the Fuzzy Lookup task, you can change the name of the _Similarity and _Confidence fields. This would not exclude them from the data flow, but would solve the duplicate name issue.

Frank

|||

Frank,

I do not find any options to re-name the _Similarity and _Confidence fields from the Advanced tab of the Fuzzy Lookup Transformation Editor. Can you please be more specific?

Thank you for the comment.

|||

On the 'Input and Output properties' tab -'Fuzzy Lookup Output'-'Output Columns'

Use the 'Name' property to rename the fields.

Frank

|||Thanks Frank. That's exactly what I was looking for.

multiple fields from common lookup table

I haven't written SQL in some time and I'm extremely rusty, so help
would be appreciated and thanks in advance.
I have a table (main) that has multiple fields that contain a lookup id
to a common lookup table (lookup). How do I write the query to pull
back one result set that has the form below?
select ?
where main.key = @.key
result set
--
key (from main)
behavior description (from lookup)
appearance description (from lookup)
other description (from lookup)
main
--
key
behaviorID
appearanceID
otherID
lookup
--
lookupID
descriptionPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Sba,
Try:
DECLARE @.KEYVAL INT
SET @.KEYVAL = 1
SELECT KEYVAL, L1.DESCRIPTION AS 'BEHAVIOR', L2.DESCRIPTION AS 'APPEARANCE',
L3.DESCRIPTION AS 'OTHER'
FROM MAIN M
JOIN LOOKUP L1
ON M.BEHAVORID = L1.LOOKUPID
JOIN LOOKUP L2
ON M.APPEARANCEID = L2.LOOKUPID
JOIN LOOKUP L3
ON M.OTHERID = L3.LOOKUPID
WHERE KEYVAL = @.KEYVAL
HTH
Jerry
"sba" <pub9@.s105192480.onlinehome.us> wrote in message
news:1129764599.221796.33700@.g49g2000cwa.googlegroups.com...
>I haven't written SQL in some time and I'm extremely rusty, so help
> would be appreciated and thanks in advance.
> I have a table (main) that has multiple fields that contain a lookup id
> to a common lookup table (lookup). How do I write the query to pull
> back one result set that has the form below?
> select ?
> where main.key = @.key
>
> result set
> --
> key (from main)
> behavior description (from lookup)
> appearance description (from lookup)
> other description (from lookup)
>
> main
> --
> key
> behaviorID
> appearanceID
> otherID
>
> lookup
> --
> lookupID
> description
>|||Looks like you have the famous One True Lookup Table. A classic database
design error.
Anyway, you can join multiple times to the same table:
SELECT M.key, T1.description, T2.description, T3.description
FROM main AS M
JOIN lookup AS T1
ON M.behaviourid = T1.id
JOIN lookup AS T2
ON M.appearanceid = T2.id
JOIN lookup AS T3
ON M.otherid = T3.id ;
David Portas
SQL Server MVP
--

Multiple columns in table that reference 1 lookup table

Hello,

I have a query that I need help with.

there are two tables...

Product
- ProductId
- Property1
- Property2
- Property3

PropertyType
- PropertyTypeId
- PropertyType

There many columns in (Product) that reverence 1 lookup table (PropertyType)

In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeId

How do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3

ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3

PropertyType(1) = PropertyType for Property1
PropertyType(2) = PropertyType for Property2
PropertyType(3) = PropertyType for Property3

I hope this makes sence.

Thanks in advance.

SELECT Product.ProductId, Product.Property1, Product.Property2, Product.Property3,
Prop1.PropertyType, Prop2.PropertyType, Prop3.PropertyType
FROM Product
INNER JOIN PropertyType Prop1 ON Product.Property1 = PropertyType.PropertyTypeId
INNER JOIN PropertyType Prop2 ON Product.Property2 = PropertyType.PropertyTypeId
INNER JOIN PropertyType Prop3 ON Product.Property3 = PropertyType.PropertyTypeId

|||

Hello,

Two approaches:

1. Left join

SELECT t0.ProductId, t0.Property1, t0.Property2, t0.Property3, t1.PropertyType1, t2.PropertyType2, t3.PropertyType3 from PRODUCT t0 LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType1
FROM Product INNER JOIN PropertyType ON Product.Property1 = PropertyType.PropertyTypeId) t1 ON t0.ProductId= t1.ProductId
LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType2
FROM Product INNER JOIN PropertyType ON Product.Property2 = PropertyType.PropertyTypeId) t2 on t0.ProductId= t2.ProductId
LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType3
FROM Product INNER JOIN PropertyType ON Product.Property3 = PropertyType.PropertyTypeId) t3 on t0.ProductId= t3.ProductId

2. Select Case

SELECT ProductId, Property1, Property2, Property3, CASE WHEN Property1 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property1) END AS propertyType1,
CASE WHEN Property2 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property2) END AS propertyType2, CASE WHEN Property3 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property3) END AS propertyType3

FROM Product

|||Thank you all for your help. I have it working.