Creating a view with multiple join criteria

This is a silly question but i must present it.
I am creating a view between two tables. Both tables are identified by
two pieces of data:
User ID and Product Name
fooshly those who created the tables did not create the Product name to
be similar. Now i'm trying to join on dissimilar information. I
cannot change the original tables, and i'm stuck making reports trying
to validate information with these joins.
one piece of information. If i can restrict product names to the first
three characters then i can do a successful table join. I can't think
for the life of me how to do it.. any suggestions?Try this:
CREATE VIEW vw_ViewName
WITH SCHEMABINDING
AS
SELECT A.[FieldName], B.[FieldName], (etc.....)
FROM TableA A
JOIN TableB B
ON Left(A.ProductName, 3) = Left(B.ProductName, 3)
AND (A.UserID = B.UserID)
"epikto" <epikto@.gmail.com> wrote in message
news:1142636261.915610.295790@.i39g2000cwa.googlegroups.com...
> This is a silly question but i must present it.
> I am creating a view between two tables. Both tables are identified by
> two pieces of data:
> User ID and Product Name
> fooshly those who created the tables did not create the Product name to
> be similar. Now i'm trying to join on dissimilar information. I
> cannot change the original tables, and i'm stuck making reports trying
> to validate information with these joins.
> one piece of information. If i can restrict product names to the first
> three characters then i can do a successful table join. I can't think
> for the life of me how to do it.. any suggestions?
>|||Excellent. I knew it was simple... Just couldn't find the right syntax
with SQL. Thank you!sql