asked
Inner join table from one column to two columns
I have two tables I am wanting to inner join. The column of one is Ordind000.procplanid . This has the job, the J000012345, and suffix, the .00001 both in this one column. But in the Job table that i need to join it to both the job and the suffix are separate columns. My guess is i need to use the replace command and write it to a temp table, but i am hoping for a much easier solution. I will also need to strip the decimal at the beginning of the job and suffix numbers in the proclanid columns i think. ![alt text][1] [1]: /storage/temp/3343-image-5.png
The underlying issue is, as you've discovered, that storing multiple different pieces of information in the one column is always going to complicate your life later on. Taken to it's illogical limits you could have a table with two columns (or even one) and a whole bunch different information and obscure delimiters that you have to negotiate to find the data you require. Some people call this XML :-). We had one vendor sell our CEO a system for hundreds of thousands of dollars. One memorable table had two columns. One called ID, the other called "data" (varchar(7896)). Data had seven levels of bespoke undocumented, nested delimiters that you had to traverse to get at the data you were after. Point is sometimes you inherit bad design that you can't change. But if there's any possibility at all of fixing the problem closer to the source than the join in question here, that will make your life and the life of everyone who follows you, easier in the long run.
It is possible to have functions and conversions in `JOIN` conditions; however, it's not necessarily a good way to go. However, a quick & dirty example to show the basics: -- just setting up a couple of tables with some really inconsistent data... DECLARE @JobPlan TABLE ( ProcPlanID VARCHAR(30) , OtherData VARCHAR(50) ); DECLARE @Job TABLE ( JobID VARCHAR(20) , Suffix INT , OtherJobData VARCHAR(50) ); INSERT INTO @JobPlan VALUES ( 'J007.001', 'Bob Holness' ), ( 'J007.002', 'Sean Connery' ), ( 'J008.001', 'Sean Bean' ); INSERT INTO @Job VALUES ( 'J007', 1, 'Casino Royale' ), ( 'J007', 2, 'The Man with the Golden Gun' ), ( 'J008', 1, 'Goldfinger' ); -- This is where we're doing a join similar to the one you want SELECT * FROM @JobPlan jp LEFT JOIN @Job j ON j.JobID = LEFT(jp.ProcPlanID, CHARINDEX('.', jp.ProcPlanID) - 1) AND j.Suffix = CONVERT(INTEGER, RIGHT(jp.ProcPlanID, 3)); As you can see in that JOIN statement, we're taking the left few characters (based on the position of the '.' - you might want to use the position of a space); the other part of the join is me knowing that the suffix is restricted to three characters, so I'm just grabbing those. If you can't guarantee that, then a nest of REVERSEs and a CHARINDEX might help, but that's another query for another day.
18 People are following this question.
