SQL join with negatives

aapjzz
aapjzz used Ask the Experts™
on

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
where ...

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
select t1.stuff, case when t1.ID_1 > 0 then t2.MoreStuff else t3.MoreStuff end MoreStuff
from table_1 t1
  join table_2 t2 on t1.ID_1 = t2.ID_1
  join table_2 t3 on t1.ID_1 = -t2.ID_2
Oracle dba
Top Expert 2009
Commented:
oops typo
select t1.stuff, case when t1.ID_1 > 0 then t2.MoreStuff else t3.MoreStuff end MoreStuff
from table_1 t1
  join table_2 t2 on t1.ID_1 = t2.ID_1
  join table_2 t3 on t1.ID_1 = -t3.ID_2

Open in new window

Top Expert 2010
Commented:
Have you tried something like this:

SELECT t1.Stuff, t2.MoreStuff
FROM table_1 t1 INNER 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 INNER JOIN
      table_2 t2 ON ABS(t1.ID_1) = t2.ID_2
WHERE t1.ID_1 < 0
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2010

Commented:
Sorry Geert, did not see your comments before I posted :)
Commented:
small correction Greet.... your sql suppose to return no record, outer join is required here.

try this...


select t1.stuff, case when t1.ID_1 > 0 then t2.MoreStuff else t3.MoreStuff end MoreStuff
from table_1 t1
  left outer join table_2 t2 on t1.ID_1 = t2.ID_1
 left outer join table_2 t3 on t1.ID_1 = -t3.ID_2

Author

Commented:
Alot of the suggestions are the same I got from Stack Overflow: http://stackoverflow.com/questions/1153779/sql-join-problem-with-negatives - I'll try share the points over all of you - thanks :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial