sql join problem with negatives

Question

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 separate 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 a positive 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:

select t1.Stuff, t2.MoreStuff from table_1 t1
join table_2 t2 on t1.ID_1 = t2.ID_1
where ...

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

The only other idea I've had is a separate sql statement to handle these odd ones. This is all being run by clr sql from C#. Adding a separate SqlCommand to the code will most likely slow things down hence why I'd prefer to keep it all in one command.

Your input is welcome, thanks :)

Answer 1

It will have to be something like

select t1.Stuff, t2.MoreStuff from table_1 t1, table_2 t2 where (t1.ID_1 = t2.ID_1 OR t1.ID_1 = CONCAT("-",t2.ID_1)) where ...

Not sure if I have misunderstood your question.

Answer 2

By applying left joins across table two and using the absolute value function, you should be able to accomplish what you're looking for:

SELECT t1.Stuff, isnull(t2.MoreStuff, t2_2.MoreStuff) 
FROM table_1 t1
    LEFT JOIN table_2 t2     ON t1.ID_1 = t2.ID_1 
                             AND t1.ID_1 > 0
    LEFT JOIN table_2 t2_2   ON abs(t1.ID_2) = t2_2.ID_2 
                             AND t1.ID_2 < 0
WHERE 
   ...

The caveat here is that if ID_1 and ID_2 are not mutually exclusive you will get 2 query results.

Answer 3

Simpliest way - join these tables using UNION ALL:

select t1.Stuff, t2.MoreStuff from table_1 t1
join table_2 t2 on t1.ID_1 = t2.ID_1
where t1._ID_1>0
UNION ALL
select t1.Stuff, t2.MoreStuff from table_1 t1
join table_2 t2 on abs(t1.ID_1) = t2.ID_2
where t1._ID_1<0

Answer 4

This won't be very performant...but then, nothing will. You need to transform your negative key into a positive one, and conditional logic for the join. Like this:

select t1.Stuff, t2.MoreStuff 
from table_1 t1
join table_2 t2 on (t1.ID_1 > 0 AND t1.ID_1 = t2.ID_1)
  OR (t1.ID_1 <0 AND ABS(t1.ID_1) = t2.ID_2)
where ...

No chance of using an index, because you're transforming t1.ID_1 (with the ABS function), but it's the best that you can do given the circumstances.

Answer 5

You can do something like this, but only after introducing the schema designer to a LART:

SELECT
    t1.stuff, COALESCE(t2a.morestuff, t2b.morestuff)
  FROM
    table_1 t1
    LEFT JOIN table_2 t2a ON (t1.id_1 > 0 AND t1.id_1 = t2a.id_1)
    LEFT JOIN table_2 t2b ON (t1.id_1 < 0 AND t1.id_1 = -1 * t2b.id_2)
  // etc

Alternatively,

SELECT
    t1.stuff, t2.morestuff
  FROM
    table_1 t1
    LEFT JOIN table_2 t2 ON (
      (t1.id_1 > 0 AND t1.id_1 = t2.id_1)
      OR (t1.id_1 < 0 AND t1.id_1 = -1 * t2.id_2)
    )
  // etc

Remember the LART, that's the most important part!

Answer 6

Let's say the tables look like this:

Table1 (id INT, foo INT, fk INT)

Table2 (id1 INT, id2 INT, bar VARCHAR(100))

...where fk can be used to look up a row in Table2 using id1 if positive and id2 if negative.

Then you can do the join as follows:

SELECT T1.id, T1.foo, T2.bar
FROM Table1 T1 INNER JOIN Table2 T2
ON    (T1.fk > 0 AND T2.id1 = T1.fk)
   OR (T1.fk < 0 AND T2.id2 = - T1.fk)

Answer 7

select t1.Stuff, t2.MoreStuff from table_1 t1
join table_2 t2 on t1.ID_1 = t2.ID_1 or -t1.ID_1 = t2.ID_2
where ...

Answer 8

try this

DECLARE @Table TABLE(
     ID INT,
     ForeignKeyID INT
)

INSERT INTO @Table (ID,ForeignKeyID) SELECT 1, 1
INSERT INTO @Table (ID,ForeignKeyID) SELECT 2, 2
INSERT INTO @Table (ID,ForeignKeyID) SELECT 3, -1
INSERT INTO @Table (ID,ForeignKeyID) SELECT 4, -2

DECLARE @ForeignTable TABLE(
     ID_1 INT,
     ID_2 INT,
     Val VARCHAR(MAX)
)

INSERT INTO @ForeignTable (ID_1,ID_2,Val) SELECT 1, 11, '1'
INSERT INTO @ForeignTable (ID_1,ID_2,Val) SELECT 2, 22, '2'
INSERT INTO @ForeignTable (ID_1,ID_2,Val) SELECT 3, 1, '3'
INSERT INTO @ForeignTable (ID_1,ID_2,Val) SELECT 3, 2, '4'

SELECT  *
FROM    @Table t INNER JOIN
     @ForeignTable ft ON ABS(t.ForeignKeyID) =
          CASE 
           WHEN t.ForeignKeyID > 0
            THEN ft.ID_1
           ELSE
            ft.ID_2
          END