SQL join

Hello I have one Project number 0110 in one table but in other table its 0110A-B so when i do a join between these 2 tables with proj number the above example wont come in the output..so how can i get the above ex also in my output

more ▼

asked Jul 10, 2017 at 08:32 AM in Default

avatar image

21 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You will have to define how these 2 project numbers can differ. Is it always 'A-B' that's added to the end? Or could it differ? Is the project number always 4 digits? Does the 4 digit project number always start the value in the other table?

This will help you decide how to make the join condition, so it for example it could be

 --append 'A-B'
 on Table1.ProjectNumber +'A-B'= Table2.ProjectNumber  


 -- match on first 4 characters
 on Table1.ProjectNumber = left(Table2.ProjectNumber, 4)  


 -- match on starting characters
  on Table1.ProjectNumber like Table2.ProjectNumber+'%'  

or something else

more ▼

answered Jul 10, 2017 at 08:58 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Thank you Kev,

Jul 10, 2017 at 10:11 AM prince1988

If this solves your problem @prince1988, it's nice to vote up and mark as answered :)

Jul 10, 2017 at 12:56 PM Mart
(comments are locked)
10|1200 characters needed characters left

Although the above by Kev works fine; I have issues with putting string functions on the joins. This can have a huge performance impact and can result with poor plans being cached. You are better off cleaning your data first. e.g. use a CTE with what Kev suggested to clean your data and perform your JOIN with the CTE.

more ▼

answered Sep 03, 2017 at 01:39 PM

avatar image

81 3 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 10, 2017 at 08:32 AM

Seen: 317 times

Last Updated: Sep 03, 2017 at 01:39 PM

Copyright 2018 Redgate Software. Privacy Policy