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!
SQL join with negatives
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 ...
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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
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
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
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
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
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 :)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial