May 20, 2009 at 6:10 am
How can I render the following SQL spec which includes an 'OR' statement?
Here is the specification I need to fulfill:
WHERE (Table_A.Col_1=Table_B.Col_1 AND Table_B.Col_2=Table_D.Col_2)
OR (Table_A.Col_2=Table_C.Col_2 AND Table_C.Col_2=Table_D.Col_2)
I tried using INNER JOINS but I do not know how to accomodate the 'OR' in an INNER JOIN here.
SELECT * FROM Table_A A
INNER JOIN Table_B B ON A.Col_1 = B.Col_1
INNER JOIN Table_D D ON rtrim(B.Col_2) = rtrim(D.Col_2)
OR --<<< DOES NOT WORK INNER JOIN Table_C C ON C.Col_2 = A.Col_2
AND CW.Col_2 = D.Col_2
May 20, 2009 at 6:20 am
I added a UNION of the 2 mutually exclusive conditions..
Select ...
INNER JOIN
INNER JOIN
UNION
Select ...
INNER JOIN
INNER JOIN
and that seems to have resolved my issue....
May 20, 2009 at 5:37 pm
Would not an OUTER join solution work too?Select *
from Table_A A
left outer Join Table_B B on B.col_1 = A.col_1
left outer Join Table_C C on C.col_2 = A.col_2
left outer Join Table_D D on D.col_2 = B.col_2 or D.col_2 = C.col_2
where D.col_2 is not NULL
The UNION depends on Table_B and Table_C having the same data layouts. The outer join solution would force the use of selective logic to use columns from either of those tables (case statements in the select or somthing downstream). That, of course, is what you already have in the original code with the join implied by the WHERE clause.
May 20, 2009 at 9:46 pm
john.arnott (5/20/2009)
Would not an OUTER join solution work too?
[font="Verdana"]I would use an outer join as well, but perhaps one slight variation (probably just in how I think, I doubt it would make a change to performance):
select *
from Table_A A
left outer join Table_B B on B.col_1 = A.col_1
left outer join Table_C C on C.col_2 = A.col_2
inner join Table_D D on D.col_2 = coalesce(B.col_2, C.col_2);
[/font]
May 20, 2009 at 11:37 pm
Bruce, I like your solution better, if only for readability, as it keeps all the join criteria together. My suggestion may work OK, but by the time all the rest of a "real world" query gets piled into the code, that test on table_D.col2 could get lost in the WHERE clause. Thanks for not passing on the chance to share what you may have considered a trivial difference -- I certainly appreciate it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply