Inner join with like

Question

4.00/5 (1 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:

SQL

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 23-Mar-16 3:37am

Samira Radwan

Updated 23-Mar-16 3:57am

v2

Add a Solution

Comments

Herman<T>.Instance 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Homero Rivera · Accepted Answer · 2016-03-23T03:57:00

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

SQL

... 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

SQL

... 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).