Click here to Skip to main content
14,643,428 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
I have 2 vehicles tables like (year/make/model/trim/EngineCylinder/transmission).
trying to join the 2 tables using inner join on year/make/model/transmission, but the cylinder is not really equal. In tb1 cylinder will be something like (8cyl. / 4cyl.), in tb2 cylinder will be part of the field value something like (coupe 4cyl. manaul).
I tried to use LIKE but it doesn't work although cylinder in tb1 is part of tb2 cylinder field vale.

I have 2 question:
1- How to use Like with inner join to get all the rows that has the same cylinder number (which i have tried!)
2- is it possible to trim the field in tb2 to get only the cylinder No. so (coupe 4cyl. manaul) will be just (4cyl).
Edit: using SQL server 2008 R2

thanks,
Samira

What I have tried:

Here is what I have tried to inner join the 2 tables which work perfect without cylinder:
Select table1.* tb1
inner join table2 tb2
on tb1.year=tb2.year
and tb1.make=tb2.make
and tb1.model=tb2.model 
and tb1.transmission=tb2.transmission
and (tb1.EngineCylinder like '%' + tb2.EngineCyliner + '%') --or use where 
-- also tried: and tb1.EngineCylinder in (select EngineCylinder from tb2)
Posted
Updated 23-Mar-16 3:57am
v2
Comments
digimanus 23-Mar-16 9:55am
   
set the latest AND to a WHERE clause
Samira Radwan 23-Mar-16 11:11am
   
thanks! I've already tried where, @Homero Rivera solution does it.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If I read correctly, the EngineCylinder in tb2 is more complex than tb1's.
LIKE will check whether the first string contains the second one.

If you switch from your current expression to
... and (tb2.EngineCyliner LIKE '%' + tb1.EngineCylinder + '%')

That should work.
Or you can also use patindex which gives you the starting position of text pattern within another
... and PATINDEX('%' + tb1.EngineCylinder + '%', tb2.EngineCyliner) >= 1


EDIT
From my latest studies in MS SQL Server; Although this works it is inconvenient to use a predicate with a like or a function in a JOIN or the WHERE clause because the database engine can't benefit from indexes, and will have to execute that operation for every row in the table... Really bad for huge tables, and really bad if this is a production environment such as hundreds of users per second. MS SQL Server does have features to help this kind of search, for example FULL TEXT SEARCH (google for it).
   
v2
Comments
Samira Radwan 23-Mar-16 11:11am
   
thanks a lot! PATINDEX does it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100