Creating a view with multiple join criteria

17 Mar 2006 10:57 PM

epikto

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?

17 Mar 2006 11:08 PM

Andrew

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)

Show quote

"epikto" <epi***@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?
>

20 Mar 2006 3:05 PM

epikto

Excellent. I knew it was simple... Just couldn't find the right syntax
with SQL. Thank you!