0

I've got an old database dropped in my knee and it's not very well modeled. The crappy modelling is taken care of in an overly complex software application, but I need to work more closely to the database.

Instead of understanding how foreign keys and relations work, the database is modeled like this:

  • Table1 contains ID_1, Value
  • Table2 contains ID_2, AnotherValue, ID_1s

where ID_1 is an INT and ID_1s is a varchar containing a pipe-separated string of references to ID_1 (i.e. '1|4|5', meaning it is referenced to rows where table1.ID_1 is 1, 4, or 5)

Now, I need to join these two tables rather than have an application parse through them and link them programatically. At the same time, I can't modify the db-model itself since it will break the application.

Bsaically, I need to emulate a many-to-many relation with what I've got.

Does anyone have an idea of a somewhat solid way of doing this?

(In the future, there is a plan to basically redesign the database and use views as an abstraction for the application.)

CB Du Rietz
  • 167
  • 1
  • 8
  • What about creating a table function that splits the varchar by "|" character and perform the join from this function? – Ivan Sivak Dec 30 '14 at 13:56
  • Something like this? http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco – xQbert Dec 30 '14 at 13:56
  • Are you developing new code which will use your new virtual many-to-many relation? If so, what are restrictions in changing your data model? Why it is a problem to add new view? – Serg Dec 30 '14 at 14:31
  • It's an ad-hoc query, but I will probably need it more than once in the near future. The long-term plan is to to a complete rewrite, though. – CB Du Rietz Dec 30 '14 at 15:01
  • A problem with views to mitigate the problem is that the underlying database is inconsistent due to the bad db design... – CB Du Rietz Dec 30 '14 at 15:12

2 Answers2

1

While it isn't going to be the fastest of queries, you can apply the same idea as outlined here: Parameterize an SQL IN clause

SELECT Table1.Value
FROM Table1
INNER JOIN Table2
    ON '|' + Table2.ID_1s + '|' LIKE '%|' + CAST(Table1.ID_1 AS varchar(20)) + '|%'

-- Add any other conditions in a WHERE clause.
Community
  • 1
  • 1
Jason Faulkner
  • 5,933
  • 2
  • 22
  • 32
0

I think you could use something like this

SELECT 
  Table1.*,
  Table2.*
FROM
  Table1
  INNER JOIN Table2
    ON PATINDEX('%|'+LTRIM(RTRIM(CONVERT(NVARCHAR(100), Table1.ID_1)))+'|%', Table2.ID_1s) <> 0