I need to select data from two table using a join. This is fairly simple and have no problems here. The problem occurs when the field I am joining is used as two seperate foreign keys (I didn't design this). So the ID field that I join on is either a positive or negative number.
If it's a positive number it relates to ID_1 on the table_2 table, if it's a negative, the number relates to ID_2 on the table_2 table. However the ID_2 will be apositive number (even though it's stored as a negative in the foreign key). Obviously there are no constraints to enforce these - so in essence not real foreign keys :/
The SQL I'm using goes something like this and is fine for the positive numbers: <see code>
How to incorporate the negative aspect of this into the join. Is this even possible? Ideally I'd like to alter the table to my needs but apparently this is not a valid option. I'm well and truely stuck.
The only other idea I've had is a seperate sql statement to handle these odd ones. This is all being run by clr sql from C#. Adding a seperate SqlCommand to the code will most likely slow things down hence why I'd prefer to keep it all in one command.
You input is welcome, thanks :)
select t1.Stuff, t2.MoreStuff from table_1 t1
join table_2 t2 on t1.ID_1 = t2.ID_1